Modify element value for typed and untyped xml

First, let’s define an untyped xml and modify it:

 

declare @x xml

SET @x=N

<Properties xmlns="http://www.xyz.com/p01"&gt;

      <Version>1.0.0</Version>

      <MyProperties>

      <Property>P1</Property>

      </MyProperties>

</Properties>’

/*

— comment 1

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version/text())[1] with  "2.0.0"’);

*/

/*

— comment 2

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version)[1] with  "2.0.0"’);

*/

SELECT @x;

GO

Uncomment the first comment block, run the script, it works fine. The version numbe in the xml is modified with the new value.

Commnet the first comment block and uncomment the second block, run the sript it gives the following error:

Msg 2356, Level 16, State 1, Line 16

XQuery [modify()]: The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content, found ‘element(p{http://www.xyz.com/p01&#125;:Version,xdt:untyped) ?’

Now let’s create an xml scehma in order to define a typed xml  

USE TEMPDB

GO

declare @xs xml

SET @xs=N

<xs:schema id="Test" targetNamespace="http://www.xyz.com/p01&quot;

      xmlns:xs="http://www.w3.org/2001/XMLSchema&quot;

      xmlns:p="http://www.xyz.com/p01&quot;

      attributeFormDefault="unqualified" elementFormDefault="qualified">

 

      <xs:simpleType name="typeVersion">

            <xs:restriction base="xs:string">

                  <xs:pattern value="[0-9]{1,2}\.([0-9]){1,2}\.([0-9]){1,2}" />

            </xs:restriction>

      </xs:simpleType>

 

      <xs:element name="Properties">

            <xs:complexType>

                  <xs:sequence>

                        <xs:element name="Version" type="p:typeVersion" />

                        <xs:element name="MyProperties">

                              <xs:complexType>

                                    <xs:sequence>

                                          <xs:element name="Property" type="xs:string" maxOccurs="unbounded" />

                                    </xs:sequence>

                              </xs:complexType>

                        </xs:element>    

                  </xs:sequence>

            </xs:complexType>

      </xs:element>

</xs:schema>

 

CREATE XML SCHEMA COLLECTION dbo.TestSchema AS @xs;

GO

We will focus on the two elements Version and Property in the schema. Version is defined by a simple type p:typeVersion, while Property is defined as a W3C data type xs:string. We will see we need to use different method to modify the values of the two elements.

declare @x xml (dbo.TestSchema)

SET @x=N

<Properties xmlns="http://www.xyz.com/p01"&gt;

      <Version>1.0.0</Version>

      <MyProperties>

      <Property>P1</Property>

      </MyProperties>

</Properties>’

SELECT @x;

 

/*

— comment 1

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version)[1] with  "P10"’);

 

SELECT @x;

*/

/*

— comment 2

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version/text())[1] with  "P10"’);

 

SELECT @x;

*/

 

/*

— comment 3

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:Version)[1] with  "2.0.0" cast as p:typeVersion?’);

 

SELECT @x;

 

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:MyProperties/p:Property)[1] with  "P10"’);

 

SELECT @x;

*/

 

/*

— comment 4

SET @x.modify(‘

declare namespace p="http://www.xyz.com/p01&quot;;

replace value of (/p:Properties/p:MyProperties/p:Property/text())[1] with  "P10"’);

 

SELECT @x;

*/

 

Run comment 1 block (uncomment the block and run it), it gives the following error. It means the value “2.0.0” is of xs:string. The expected type is p:typeVersion.

Msg 2247, Level 16, State 1, Line 13

XQuery [modify()]: The value is of type "xs:string", which is not a subtype of the expected type "p{http://www.xyz.com/p01&#125;:typeVersion".

 

Run comment 2 block (comment other comment blocks and uncomment this block and run it), it gives the following error. It says text() is not supported for typed xml in modify function.

Msg 9312, Level 16, State 1, Line 20

XQuery [modify()]: ‘text()’ is not supported on simple typed or ‘http://www.w3.org/2001/XMLSchema#anyType&#8217; elements, found ‘element(p{http://www.xyz.com/p01&#125;:Version,p{http://www.xyz.com/p01&#125;:typeVersion) *’.

 

Run comment 3 block, it works fine. The code casts the xs:string “P10” to p:typeVersion. Note the cast syntax, it requires a question mask ? at the end. The modification for p:property also works because its value is xs:string.

Run comment 4 block, which adds text() after the element xpath. it gives the same error as comment 2 block.

Msg 9312, Level 16, State 1, Line 46

XQuery [modify()]: ‘text()’ is not supported on simple typed or ‘http://www.w3.org/2001/XMLSchema#anyType&#8217; elements, found ‘element(p{http://www.xyz.com/p01&#125;:Property,xs:string) *’.

 

To summary:

For untyped xml, to modify element value, we need to attach text() function after the elemnt xpath. Without text(), SQL server gives error;

For typed xml,  text() is not supported. Only the xpath without text() is reuqired;

If the element is of W3C “http://www.w3.org/2001/XMLSchema” data type, the value can be specified directly;

If the element is of user defined type, we need to use cast funtion to cast the value to the defined type:

"Value" cast as UserDefinedType?

 

Posted in XML, XQuery and XPath | 2 Comments

SMO and Adhoc Queries

Start SQL Profiler, chose SQL:BatchCompleted event only and set filter on TextData to ‘%product%’. In SSMS, go to AdventureWorks database, right click Production.Product table–>Script Table as –>Create to–>Clipboard. Go back SQL profiler, you will find 23 adhoc calls sent to database. Some queries are pretty lengthy and total bytes sent via wire is about 15KB. If you are scripting a database with hundreds of tables, you can imaging the load it adds to the network and the server.
Besides, sine they are adhoc queries, SQL Server generates lots of adhoc plans and caches them but cannot be reused.
The following query lists the adhoc plans generated for the scripting query:

SELECT * FROM sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) t
where p.objtype=’adhoc’
 and t.text like ‘%product%’
 and t.text not like ‘%dm_exec_cached_plans%’
 
The total cach memory used:
SELECT sum(size_in_bytes) FROM sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) t
where p.objtype=’adhoc’
 and t.text like ‘%product%’
 and t.text not like ‘%dm_exec_cached_plans%’
 
It gives 4038656Bytes (about 4 MB)
It makes no sense to send large amount queries over the wire, especially when scripting morte than one tables sending the similar queries again and again. Create stored procedures and call them from smo makes more sense to me. It saves network bandth, makes reusable cach, and the server is more responsive during the scripting time.
 
Due to above mentioned reason, I made the suggestion on sql server connect:
 
Hopefully it can catch someone’s eyes in MS.
 
 
Posted in SQL Server 2005 SMO | Leave a comment

ORDER BY and CASE Expression

/*

We can use CASE expression in ORDER BY clause. This is useful if a stored procedure has parameters from client side to specify ordering column and direction, e.g.:

*/

USE

AdventureWorks

GO

DECLARE @SortOrder nvarchar(5), @OrderBy sysname;

SELECT @SortOrder=N‘ASC’, @OrderBy=N‘ManagerID’

SELECT * FROM HumanResources.Employee

ORDER BY CASE WHEN @SortOrder=‘DESC’ THEN

CASE @OrderBy WHEN ‘ContactID’ THEN ContactID WHEN ‘EmployeeID’ THEN EmployeeID WHEN ‘ManagerID’ THEN ManagerID END

END DESC,

CASE WHEN @SortOrder=‘ASC’ THEN

CASE @OrderBy WHEN ‘ContactID’ THEN ContactID WHEN ‘EmployeeID’ THEN EmployeeID WHEN ‘ManagerID’ THEN ManagerID END

END ASC

GO

/*

A user can specify order by ContactID, EmployeeID, ManagerID and sort direction. In the execution plan, SQL Server evaluates the order by expressions. In the given value, the syntax is literally equivalent to ORDER BY NULL DESC, ManagerID ASC,

and the first order by column is ignored since no definition (NULL). I said litterally because if you write the query directly in the same syntax, it will fail:

Msg 408, Level 16, State 1, Line 1

A constant expression was encountered in the ORDER BY list, position 1.

*/

SELECT

*  FROM HumanResources.Employee

ORDER

BY NULL DESC, ManagerID ASC;

/*

Part of the execution plan:

  1. <OrderBy>
  2. <OrderByColumn Ascending="false">
  3. <ColumnReference Column="Expr1003" />
  4. </OrderByColumn>
  5. <OrderByColumn Ascending="true">
  6. <ColumnReference Column="Expr1004" />
  7. </OrderByColumn>
  8. </OrderBy>

 

  1. <DefinedValues>
  2. <DefinedValue>
  3. <ColumnReference Column="Expr1003" />
  4. <ScalarOperator ScalarString="CASE WHEN [@SortOrder]=N’DESC’ THEN CASE WHEN [@OrderBy]=N’ContactID’ THEN [AdventureWorks].[HumanResources].[Employee].[ContactID] ELSE CASE WHEN [@OrderBy]=N’EmployeeID’ THEN [AdventureWorks].[HumanResources].[Employee].[EmployeeID] ELSE CASE WHEN [@OrderBy]=N’ManagerID’ THEN [AdventureWorks].[HumanResources].[Employee].[ManagerID] ELSE NULL END END END ELSE NULL END">
  5. <IF>
  6. <Condition>
  7. <ScalarOperator>
  8. <Compare CompareOp="EQ">
  9. <ScalarOperator>
  10. <Identifier>
  11. <ColumnReference Column="@SortOrder" />
  12. </Identifier>
  13. </ScalarOperator>
  14. <ScalarOperator>
  15. <Const ConstValue="N’DESC’" />
  16. </ScalarOperator>
  17. </Compare>
  18. </ScalarOperator>
  19. </Condition>
  20. <Then>
  21. <ScalarOperator>
  22. <IF>
  23. <Condition>
  24. <ScalarOperator>
  25. <Compare CompareOp="EQ">
  26. <ScalarOperator>
  27. <Identifier>
  28. <ColumnReference Column="@OrderBy" />
  29. </Identifier>
  30. </ScalarOperator>
  31. <ScalarOperator>
  32. <Const ConstValue="N’ContactID’" />
  33. </ScalarOperator>
  34. </Compare>
  35. </ScalarOperator>
  36. </Condition>
  37. <Then>
  38. <ScalarOperator>
  39. <Identifier>
  40. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ContactID" />
  41. </Identifier>
  42. </ScalarOperator>
  43. </Then>
  44. <Else>
  45. <ScalarOperator>
  46. <IF>
  47. <Condition>
  48. <ScalarOperator>
  49. <Compare CompareOp="EQ">
  50. <ScalarOperator>
  51. <Identifier>
  52. <ColumnReference Column="@OrderBy" />
  53. </Identifier>
  54. </ScalarOperator>
  55. <ScalarOperator>
  56. <Const ConstValue="N’EmployeeID’" />
  57. </ScalarOperator>
  58. </Compare>
  59. </ScalarOperator>
  60. </Condition>
  61. <Then>
  62. <ScalarOperator>
  63. <Identifier>
  64. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="EmployeeID" />
  65. </Identifier>
  66. </ScalarOperator>
  67. </Then>
  68. <Else>
  69. <ScalarOperator>
  70. <IF>
  71. <Condition>
  72. <ScalarOperator>
  73. <Compare CompareOp="EQ">
  74. <ScalarOperator>
  75. <Identifier>
  76. <ColumnReference Column="@OrderBy" />
  77. </Identifier>
  78. </ScalarOperator>
  79. <ScalarOperator>
  80. <Const ConstValue="N’ManagerID’" />
  81. </ScalarOperator>
  82. </Compare>
  83. </ScalarOperator>
  84. </Condition>
  85. <Then>
  86. <ScalarOperator>
  87. <Identifier>
  88. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ManagerID" />
  89. </Identifier>
  90. </ScalarOperator>
  91. </Then>
  92. <Else>
  93. <ScalarOperator>
  94. <Const ConstValue="NULL" />
  95. </ScalarOperator>
  96. </Else>
  97. </IF>
  98. </ScalarOperator>
  99. </Else>
  100. </IF>
  101. </ScalarOperator>
  102. </Else>
  103. </IF>
  104. </ScalarOperator>
  105. </Then>
  106. <Else>
  107. <ScalarOperator>
  108. <Const ConstValue="NULL" />
  109. </ScalarOperator>
  110. </Else>
  111. </IF>
  112. </ScalarOperator>
  113. </DefinedValue>
  114. <DefinedValue>
  115. <ColumnReference Column="Expr1004" />
  116. <ScalarOperator ScalarString="CASE WHEN [@SortOrder]=N’ASC’ THEN CASE WHEN [@OrderBy]=N’ContactID’ THEN [AdventureWorks].[HumanResources].[Employee].[ContactID] ELSE CASE WHEN [@OrderBy]=N’EmployeeID’ THEN [AdventureWorks].[HumanResources].[Employee].[EmployeeID] ELSE CASE WHEN [@OrderBy]=N’ManagerID’ THEN [AdventureWorks].[HumanResources].[Employee].[ManagerID] ELSE NULL END END END ELSE NULL END">
  117. <IF>
  118. <Condition>
  119. <ScalarOperator>
  120. <Compare CompareOp="EQ">
  121. <ScalarOperator>
  122. <Identifier>
  123. <ColumnReference Column="@SortOrder" />
  124. </Identifier>
  125. </ScalarOperator>
  126. <ScalarOperator>
  127. <Const ConstValue="N’ASC’" />
  128. </ScalarOperator>
  129. </Compare>
  130. </ScalarOperator>
  131. </Condition>
  132. <Then>
  133. <ScalarOperator>
  134. <IF>
  135. <Condition>
  136. <ScalarOperator>
  137. <Compare CompareOp="EQ">
  138. <ScalarOperator>
  139. <Identifier>
  140. <ColumnReference Column="@OrderBy" />
  141. </Identifier>
  142. </ScalarOperator>
  143. <ScalarOperator>
  144. <Const ConstValue="N’ContactID’" />
  145. </ScalarOperator>
  146. </Compare>
  147. </ScalarOperator>
  148. </Condition>
  149. <Then>
  150. <ScalarOperator>
  151. <Identifier>
  152. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ContactID" />
  153. </Identifier>
  154. </ScalarOperator>
  155. </Then>
  156. <Else>
  157. <ScalarOperator>
  158. <IF>
  159. <Condition>
  160. <ScalarOperator>
  161. <Compare CompareOp="EQ">
  162. <ScalarOperator>
  163. <Identifier>
  164. <ColumnReference Column="@OrderBy" />
  165. </Identifier>
  166. </ScalarOperator>
  167. <ScalarOperator>
  168. <Const ConstValue="N’EmployeeID’" />
  169. </ScalarOperator>
  170. </Compare>
  171. </ScalarOperator>
  172. </Condition>
  173. <Then>
  174. <ScalarOperator>
  175. <Identifier>
  176. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="EmployeeID" />
  177. </Identifier>
  178. </ScalarOperator>
  179. </Then>
  180. <Else>
  181. <ScalarOperator>
  182. <IF>
  183. <Condition>
  184. <ScalarOperator>
  185. <Compare CompareOp="EQ">
  186. <ScalarOperator>
  187. <Identifier>
  188. <ColumnReference Column="@OrderBy" />
  189. </Identifier>
  190. </ScalarOperator>
  191. <ScalarOperator>
  192. <Const ConstValue="N’ManagerID’" />
  193. </ScalarOperator>
  194. </Compare>
  195. </ScalarOperator>
  196. </Condition>
  197. <Then>
  198. <ScalarOperator>
  199. <Identifier>
  200. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ManagerID" />
  201. </Identifier>
  202. </ScalarOperator>
  203. </Then>
  204. <Else>
  205. <ScalarOperator>
  206. <Const ConstValue="NULL" />
  207. </ScalarOperator>
  208. </Else>
  209. </IF>
  210. </ScalarOperator>
  211. </Else>
  212. </IF>
  213. </ScalarOperator>
  214. </Else>
  215. </IF>
  216. </ScalarOperator>
  217. </Then>
  218. <Else>
  219. <ScalarOperator>
  220. <Const ConstValue="NULL" />
  221. </ScalarOperator>
  222. </Else>
  223. </IF>
  224. </ScalarOperator>
  225. </DefinedValue>
  226. </DefinedValues>

*/

/*

One culprit is that the data type in the order by candidate columns must be same or compatible because CASE expression returns the data type based on Data Type Precedence of all the specified columns in the expression.

E.g. the following query used columns with different data type, when order by BirthDate it succeeds:

*/

DECLARE

@SortOrder nvarchar(5),@OrderBy sysname;

SELECT

@SortOrder=N‘DESC’,@OrderBy=N‘BirthDate’

SELECT

*  FROM HumanResources.Employee

ORDER

BY CASE WHEN @SortOrder=‘DESC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN BirthDate WHEN ‘EmployeeID’ THEN EmployeeID END

END DESC,

CASE WHEN @SortOrder=‘ASC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN EmployeeID WHEN ‘EmployeeID’ THEN EmployeeID END

END ASC

GO

/*

When order by Title it is failed:

Msg 241, Level 16, State 1, Line 4

Conversion failed when converting datetime from character string.

*/

DECLARE

@SortOrder nvarchar(5),@OrderBy sysname;

SELECT

@SortOrder=N‘DESC’,@OrderBy=N‘Title’

SELECT

* FROM HumanResources.Employee

ORDER

BY CASE WHEN @SortOrder=‘DESC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN BirthDate WHEN ‘EmployeeID’ THEN EmployeeID END

END DESC,

CASE WHEN @SortOrder=‘ASC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN EmployeeID WHEN ‘EmployeeID’ THEN EmployeeID END

END ASC

/*

This is becuase the data type of the CASE expressions are Datetime because Datatime ( of Birthdate) has higher Precedence than string.

Data type Precedence:

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetime
  5. smalldatetime
  6. float
  7. real
  8. decimal
  9. money
  10. smallmoney
  11. bigint
  12. int
  13. smallint
  14. tinyint
  15. bit
  16. ntext
  17. text
  18. image
  19. timestamp
  20. uniqueidentifier
  21. nvarchar (including nvarchar(max) )
  22. nchar
  23. varchar (including varchar(max) )
  24. char
  25. varbinary (including varbinary(max) )
  26. binary (lowest)

In the first query, when BirthDate is given as the order by column, the CASE Expression can convert BirthDate to datetime implicitly because it is of datetime;

In the second query, when Title is given as the order by column, the CASE Expression cannot convert Title to datetime implicitly because it is of string;

*/

 

Posted in SQL server 2005 | Leave a comment

Output xml and relational data in OPENXML

This question arose from a post in SQL server forum. Due to performance reason, it is required to use OPENXML to process a big xml documnet. But it also needs to return a specific xml document for each record.
The source xml is as @xmlData in the following sample code;
The expected ouput is as:
First record:
Camry  
It is Camry
<ns:Features xmlns:ns="http://xyz.com"><ns:Feature>Full wheel drive.</ns:Feature><ns:Feature>ABS</ns:Feature><ns:Feature>PW,PD</ns:Feature><ns:Feature>GPS</ns:Feature><ns:Feature>AC</ns:Feature></ns:Features>
 
We can combine OPENXML and xquery to achieve this.
OPENXML output meta data @mp:xmltext, which is the textual serialization of the element and its attributes, and also the subelements, as used in the overflow handling of OPENXML, i.e. it is the piece of xml that under the specified xpath in the second parameter in OPENXML. We can use xquery methods like .query on it.
 

DECLARE @xmlData XML;

set

@xmlData = ‘<?xml version="1.0"?>

<Root xmlns="http://xyz.com"&gt;

<Items>

<Item>

<Name>Camry</Name>

<Description>It is Camry</Description>

<ItemProperties>

<Rank>High</Rank>

<Color>Red</Color>

<Feature>Full wheel drive.</Feature>

<Feature>ABS</Feature>

<Feature>PW,PD</Feature>

<Feature>GPS</Feature>

<Feature>AC</Feature>

<Category>Car</Category>

<Year>2009</Year>

</ItemProperties>

</Item>

</Items>

<Items>

<Item>

<Name>Civic</Name>

<Description>It is Civic</Description>

<ItemProperties>

<Rank>medium</Rank>

<Color>Green</Color>

<Feature>ABS</Feature>

<Feature>PW,PD</Feature>

<Feature>GPS</Feature>

<Feature>AC</Feature>

<Category>Car</Category>

<Year>2010</Year>

</ItemProperties>

</Item>

</Items>

</Root>’;

Declare

@iDoc int

EXEC sp_xml_preparedocument @iDoc Output,@xmlData,‘<Root xmlns:ns="http://xyz.com"/>&#8217;

SELECT D.[Name],D.[Description],

— Construct the <Features> xml document by .query:

D

.Features.query(N

declare namespace ns="http://xyz.com&quot;;

<ns:Features>

{

for $Feature in ns:Item/ns:ItemProperties/ns:Feature

return $Feature

}

</ns:Features>

) AS Features

From OPENXML(@iDoc,‘ns:Root/ns:Items/ns:Item’,3)

With

(

[Name]

nvarchar(50) ‘ns:Name’,

[Description]

nvarchar(255) ‘ns:Description’,

Features

xml ‘@mp:xmltext’

) D

 

Exec sp_xml_removedocument @iDoc

 

Posted in XML, XQuery and XPath | Leave a comment

Pivot more than one columns

In a recent T-SQL forum post, an interest question was how to pivot two columns in a table.
The raw data is like:
[Time],[Type],[Value],[ID]
N’21:00′,N’A’,60.24,1
N’21:00′,N’B’,20.00,2
N’22:00′,N’C’,21.33,3
N’22:00′,N’D’,80.00,4
And it was required to pivot [Type] and [ID] as following:
Time    A     IDA    B    IDB    C    IDC  D     IDD
21:00  60.24  1    20.00  2     0.00  NULL 0.00  NULL
22:00  0.00   NULL  0.00  NULL  21.33 3    80.00 4
Though since SQL Server 2005 PIVOT is supported, only one column can be pivot.
We need to use the old GROUP BY method in SQL 2000 age to achieve this:
IF object_id(‘tempdb..#Data’) IS NOT NULL
DROP TABLE #Data
CREATE TABLE #Data ([Time] Datetime not null, [Type] nvarchar(10) NOT NULL, [Value] decimal(6,2) NOT NULL, [ID] int not null,PRIMARY KEY CLUSTERED([Time],[ID]))
INSERT #Data([Time],[Type],[Value],[ID])
SELECT N’21:00′,N’A’,60.24,1 UNION ALL
SELECT N’21:00′,N’B’,20.00,2 UNION ALL
SELECT N’22:00′,N’C’,21.33,3 UNION ALL
SELECT N’22:00′,N’D’,80.00,4
SELECT * FROM #Data
DECLARE @Pvt TABLE (PID int identity not null, [ID] int not null, [Type] nvarchar(10) NOT NULL PRIMARY KEY CLUSTERED(PID))
— Get the columns to be pivoted:
INSERT @Pvt ([ID],[Type])
SELECT DISTINCT [ID],[Type]
FROM #Data
DECLARE @PID int,@PIDName nvarchar(40), @ID int,@Type nvarchar(10),@SQL nvarchar(max)
SET @SQL=N’
SELECT CONVERT(nvarchar(50),D.[Time],8) AS [Time]’
SELECT @PID=MIN(PID) FROM @Pvt
WHILE @PID IS NOT NULL
BEGIN
 SELECT @PIDName=N’ID’+[Type],@ID=ID,@Type=[Type] FROM @Pvt WHERE PID=@PID
 — Pivot Type column. CASE statement needs to check all pivoting columns
 SELECT @SQL=@SQL+N’,’+nchar(13)+N’MAX(CASE WHEN D.[ID]=’+CAST(@ID AS nvarchar(30))+N’ AND D.[Type]=”’+@Type+N”’ THEN D.[Value] ELSE 0.00 END) AS [‘+@Type+N’]’
 — Pivot ID column. CASE statement needs to check all pivoting columns
 SELECT @SQL=@SQL+N’,’+nchar(13)+N’MIN(CASE WHEN D.[ID]=’+CAST(@ID AS nvarchar(30))+N’ AND D.[Type]=”’+@Type+N”’ THEN D.[ID] ELSE NULL END) AS [‘+@PIDName+N’]’
 SELECT @PID=MIN(PID) FROM @Pvt WHERE PID>@PID
END
SET @SQL=@SQL+N’
FROM #Data D
GROUP BY D.[Time]’
PRINT @SQL
EXEC(@SQL)
/*
Time    A     IDA    B    IDB    C    IDC  D     IDD
21:00  60.24  1    20.00  2     0.00  NULL 0.00  NULL
22:00  0.00   NULL  0.00  NULL  21.33 3    80.00 4
*/
If you need to pivot more than two columns, Just add the columns in @Pvt and the dynamic SQL.
 
 
Posted in SQL server 2005 | Leave a comment

Stamp records with consecutive dates

For the following data table, the requirement is to assign/stamp the same unique number to the Group column if EndDate/StartDate is consecutive for the same Medicine.
ID    Medicine  StartDate       EndDate        Group
1     1              19900601      19940630       0
2     1              19900712      19940606       0
3     1              19940607      19940905       0
4     1              19940906      19961203       0
5     1              20050806      20080318       0
6     1              20080319      20370101       0
7     2              19901211      19940228       0
8     2              19910619      19940209       0
9     2              19940210      19950831       0
10   2              19940210      19940731       0
The expected output will be (assign the first start date as the group number):
ID    Medicine  StartDate       EndDate        Group
1     1              19900601      19940630       0
2     1              19900712      19940606       19900712
3     1              19940607      19940905       19900712
4     1              19940906      19961203       19900712
5     1              20050806      20080318       20050806
6     1              20080319      20370101       20050806
7     2              19901211      19940228       0
8     2              19910619      19940209       19910619
9     2              19940210      19950831       19910619
10   2              19940210      19940731       0
It is not easy to solve by set based statement only. The basic idea is to calculate the NextStartDate and Delta of EndDate of NextStartDate for each record.
For the first record that delat is 1 (i.e., consecutive to next record), get its StartDate as @Group number, find the @eID=MAX(ID) for this unbroken consutive series
(i.e. all delta is one and with the same medicine), and update the group to @Group with the ID range. Then find next ID with delta is 1 after @eID
IF OBJECT_ID(‘tempdb..#data’) is not null
DROP TABLE #Data
CREATE TABLE #Data(ID int not null, StartDate int not null, EndDate int not null,Medicine int not null, [Group] int not null, NextStartDate int default 0, Delta int
default 0)
INSERT #Data (ID,Medicine,StartDate,EndDate,[Group])
SELECT 1, 1,         19900601,  19940630,  0 UNION ALL
SELECT 2, 1,         19900712,  19940606,  0 UNION ALL
SELECT 3, 1,         19940607,  19940905,  0 UNION ALL
SELECT 4, 1,         19940906,  19961203,  0 UNION ALL
SELECT 5, 1,         20050806,  20080318,  0 UNION ALL
SELECT 6, 1,         20080319,  20370101,  0 UNION ALL
SELECT 7, 2,         19901211,  19940228,  0 UNION ALL
SELECT 8, 2,         19910619,  19940209,  0 UNION ALL
SELECT 9, 2,         19940210,  19950831,  0 UNION ALL
SELECT 10, 2,         19940210,  19940731,  0
— Get all the NextStartDate and Delta
UPDATE A
SET NextStartDate=B.StartDate,
 Delta=B.StartDate-A.EndDate
FROM #Data A
INNER JOIN #Data B
ON A.ID=B.ID-1
 AND A.Medicine=B.Medicine
SELECT * FROM #Data
DECLARE @ID int,@eID int,@GroupID int,@Medicine int
— First record consecutive with next one
SELECT @ID = MIN(ID) FROM #Data WHERE Delta=1
WHILE @ID is not null
BEGIN
 — Get the @GroupID, @Medicine
 SELECT @GroupID=StartDate,@Medicine=Medicine FROM #Data WHERE ID=@ID
 — Find the end ID of this unbroken consecutive series
 SELECT TOP 1 @eID=ID FROM #Data WHERE ID>@ID AND Delta<>1 AND Medicine=@Medicine ORDER BY ID;
 — Update the GroupID in this range
 UPDATE #Data
 SET [Group]=@GroupID
 WHERE ID>=@ID AND ID<=@eID
 — Process next series
 SELECT @ID = MIN(ID) FROM #Data WHERE ID>@eID AND Delta=1
END
SELECT ID,Medicine,StartDate,EndDate,[Group] FROM #Data
Posted in SQL server | Leave a comment

Is the length of empty string always 0?

DECLARE @x nvarchar(max), @b varbinary(max), @v sql_variant;

SELECT

@x=,@b=cast( as varbinary(max)),@v=cast( as sql_variant)

— For those variable length data type, the length of empty string always returns 0

SELECT

DATALENGTH(@x),DATALENGTH(@b),DATALENGTH(@v);

GO

— for untyped xml:

declare

@x xml;

— You can assign empty string to an untyped xml variable

SET

@x=N

SELECT

@x

— Check its length, it is not 0! It always returns 5;

SELECT

DATALENGTH(@x)

GO

— for typed xml:

CREATE

XML SCHEMA COLLECTION dbo.TestSchema

AS

N

‘<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;

<xsd:element name="Root">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="ContactName" type="xsd:string" />

<xsd:element name="JobTitle" type="xsd:string" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>’

GO

declare

@x xml(dbo.TestSchema );

— You can even assign empty string to an typed xml variable (strange behaviour)

SET

@x=N

SELECT

@x

— Check its length, it is not 0! It always returns 5;

SELECT

DATALENGTH(@x)

GO

— This is because by default typed xml variable/column is CONTENT. 

— DECLARE xml(dbo.TestSchema) is equivalent to DECLARE @xml(CONTENT dbo.TestSchema)

— For DOCUMENT type of xml, you cannot assign a empty string:

declare

@x xml(DOCUMENT dbo.TestSchema );

— You can NOT even assign empty string to DOCUMENT type xml variable

SET @x=N

So be carefull when checking an xml variable is empty or not. Do not use Datalength(@xmlVariable)=0 to check an xml is empty or not.

GO

Posted in SQL server 2005 | Leave a comment