September 2008 - Posts
In the previous post we saw an example of the PIVOT operator introduced in SQL Server 2005. We saw a simple example that transformed rows to columns using the PIVOT operator. In this post, we will examine how to achieve this SQL Server 2000.
Here is the source data.
/*
id
-----------
1
2
3
4
5
6
7
8
9
10
11
12
*/
Here is the result we are trying to achieve.
/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
Here is the query that generates the result that we need.
SET NOCOUNT ON;
DECLARE @t TABLE (id INT)
INSERT INTO @t (id) SELECT 1
INSERT INTO @t (id) SELECT 2
INSERT INTO @t (id) SELECT 3
INSERT INTO @t (id) SELECT 4
INSERT INTO @t (id) SELECT 5
INSERT INTO @t (id) SELECT 6
INSERT INTO @t (id) SELECT 7
INSERT INTO @t (id) SELECT 8
INSERT INTO @t (id) SELECT 9
INSERT INTO @t (id) SELECT 10
INSERT INTO @t (id) SELECT 11
INSERT INTO @t (id) SELECT 12
select * from @t
SELECT
MAX(CASE WHEN col = 1 THEN id ELSE NULL END) AS Col1,
MAX(CASE WHEN col = 2 THEN id ELSE NULL END) AS Col2,
MAX(CASE WHEN col = 3 THEN id ELSE NULL END) AS Col3,
MAX(CASE WHEN col = 4 THEN id ELSE NULL END) AS Col4
FROM (
SELECT id,
FLOOR((id-0.01)/4) AS grp,
CASE WHEN id % 4 = 0 THEN 4 ELSE id % 4 END AS Col
FROM @t
) a
GROUP BY grp/*
Col1 Col2 Col3 Col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
Generating PIVOT/CROSS-TAB queries is one of the common requirements that we find in our day-to-day programming life. SQL Server 2005 introduced a new operator: PIVOT, which made generating pivot/cross-tab results much easier. Though the PIVOT operator has a number of limitations (does not support dynamic columns etc), it really made cross-tab queries much easier.
It is two years since I wrote my last post on PIVOT operator. I wrote a number of PIVOT queries in the last several months to help people in the MSDN TSQL forum. As I mentioned earlier, it is pretty easy to write a cross-tab query using the PIVOT operator. However, sometimes you will find it little tricky to achieve the desired results.
I had a discussion with my friend and SQL Server MVP, Pinal Dave about PIVOT queries this morning. We discussed a simple example and I would like to share it with you.
Here is the sample data.
id
-----------
1
2
3
4
5
6
7
8
9
10
11
12
We need to generate the following output from the above data.
/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
Here is the query that generates the above result.
SET NOCOUNT ON;
DECLARE @t TABLE (id INT)
INSERT INTO @t (id) SELECT 1
INSERT INTO @t (id) SELECT 2
INSERT INTO @t (id) SELECT 3
INSERT INTO @t (id) SELECT 4
INSERT INTO @t (id) SELECT 5
INSERT INTO @t (id) SELECT 6
INSERT INTO @t (id) SELECT 7
INSERT INTO @t (id) SELECT 8
INSERT INTO @t (id) SELECT 9
INSERT INTO @t (id) SELECT 10
INSERT INTO @t (id) SELECT 11
INSERT INTO @t (id) SELECT 12
SELECT col1, col2, col3, col4 FROM (
SELECT id,
'Col' +
CAST(
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END AS VARCHAR
) AS Col,
ROW_NUMBER() OVER (
PARTITION BY
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END ORDER BY id
) AS seq
FROM @t
) a
PIVOT (
MAX(id) FOR col IN (col1, col2, col3, col4)
) pvt
/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
Let me explain the anatomy of this query. The first part of the query generates a column that contains values 'col1','col2','col3' and 'col4'.
SELECT id,
'Col' +
CAST(
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END AS VARCHAR
) AS Col
FROM @t
/*
id Col
----------- ---------------------------------
1 Col1
2 Col2
3 Col3
4 Col4
5 Col1
6 Col2
7 Col3
8 Col4
9 Col1
10 Col2
11 Col3
12 Col4
*/
The next version of the query breaks the values into groups of 4.
SELECT id,
'Col' +
CAST(
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END AS VARCHAR
) AS Col,
ROW_NUMBER() OVER (
PARTITION BY
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END ORDER BY id
) AS seq
FROM @t
ORDER BY seq
/*
id Col seq
----------- --------------------------------- --------------------
1 Col1 1
2 Col2 1
3 Col3 1
4 Col4 1
8 Col4 2
7 Col3 2
6 Col2 2
5 Col1 2
9 Col1 3
10 Col2 3
11 Col3 3
12 Col4 3
*/
And the final version applies the PIVOT operator.
SELECT col1, col2, col3, col4 FROM (
SELECT id,
'Col' +
CAST(
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END AS VARCHAR
) AS Col,
ROW_NUMBER() OVER (
PARTITION BY
CASE
WHEN id % 4 = 0 THEN 4
ELSE id % 4
END ORDER BY id
) AS seq
FROM @t
) a
PIVOT (
MAX(id) FOR col IN (col1, col2, col3, col4)
) pvt
/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
The PIVOT operator is available only in SQL Server 2005. This post explains how to do this in SQL Server 2000.
Along with the PIVOT operator, SQL Server 2005 introduced another operator: UNPIVOT that does the opposite of PIVOT - transforming columns to rows. This post explains how to write an UNPIVOT query to achieve the original data from the PIVOT result we generated in this query.
In the previous post we discussed the PIVOT operator introduced by SQL Server 2005. We saw how to transform ROWS to COLUMNS using the PIVOT operator. This is the source data that we examined in the previous post.
/*
id
-----------
1
2
3
4
5
6
7
8
9
10
11
12
*/
Then, we generated the following output using a PIVOT query.
/*
Col1 Col2 Col3 Col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
Now, let us see how to generate the original data back using the UNPIVOT operator.
SET NOCOUNT ON;
DECLARE @t TABLE (col1 INT, col2 INT, col3 INT, col4 INT)
INSERT INTO @t (col1, col2, col3, col4)
SELECT 1, 2, 3, 4 UNION ALL
SELECT 5, 6, 7, 8 UNION ALL
SELECT 9, 10, 11, 12
SELECT * FROM @t
/*
col1 col2 col3 col4
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
9 10 11 12
*/
SELECT ID FROM @t
UNPIVOT (
ID FOR Cols IN (col1, col2, col3, col4)
) up
/*
ID
-----------
1
2
3
4
5
6
7
8
9
10
11
12
*/
We have seen a number of posts on various string operations using XQuery functions and FOR XML operator. This post explains how to match a delimited string against another delimited string.
I wanted to share a piece of code that I wrote to help a member of our User Group. I found it to be very interesting and thought of sharing with all of you. One of the reasons why I wanted to share this example is because it uses an XML approach to solve the given problem.
Let us get into the details of the problem. Here is the data from a table that we are going to query.
id Colors
----------- --------------------
1 Red
2 Red,Green
3 Green, Yellow
4 Yellow,Red,White
5 Green, White
6 White
We need to write a stored procedure that receives a comma delimited string that contains one or more color names. For example, if we receive "Red,Yellow", we need to return every row that has "Red" or "Yellow" in the "Colors" column. (All rows except 5 and 6 in this example)
I suppose there are a number of ways to write this query. One of the options is to split the incoming parameter using your favorite method (and I decided to use XML). Once we have split the parameter, we can find the appropriate values using PATINDEX().
Here is the code that performs this.
SET NOCOUNT ON;
DECLARE @t TABLE (id INT, Colors VARCHAR(20))
INSERT INTO @t (id, Colors) SELECT 1, 'Red'
INSERT INTO @t (id, Colors) SELECT 2, 'Red,Green'
INSERT INTO @t (id, Colors) SELECT 3, 'Green, Yellow'
INSERT INTO @t (id, Colors) SELECT 4, 'Yellow,Red,White'
INSERT INTO @t (id, Colors) SELECT 5, 'Green, White'
INSERT INTO @t (id, Colors) SELECT 6, 'White'
DECLARE @Filter VARCHAR(100)
SET @Filter = 'Red,Yellow'
DECLARE @xmlFilter XML
SELECT @xmlFilter =
CAST('<i>' + REPLACE(@Filter, ',', '</i><i>') + '</i>' AS XML)
SELECT DISTINCT
id,
Colors
FROM @t t
CROSS JOIN (
SELECT
x.i.value('.', 'VARCHAR(10)') AS filter
FROM @XmlFilter.nodes('//i') x(i)
) b
WHERE PATINDEX('%' + b.filter + '%', Colors) > 0
/*
id Colors
----------- --------------------
3 Green, Yellow
1 Red
2 Red,Green
4 Yellow,Red,White
*/
I have not analyzed the performance factors. The CROSS JOIN might be a little expensive. PATINDEX() is found to be performing well, in many of the tests I did in the past. In fact, I found it to be performing better than LIKE, in many cases.
Let us see how this query works.
The first part of the query converts the comma delimited string to an XML document.
SELECT @xmlFilter =
CAST('<i>' + REPLACE(@Filter, ',', '</i><i>') + '</i>' AS XML)
/*
<i>Red</i>
<i>Yellow</i>
*/
Then the inner query retrieves the values from the XML document.
SELECT
x.i.value('.', 'VARCHAR(10)') AS filter
FROM @XmlFilter.nodes('//i') x(i)
/*
filter
----------
Red
Yellow
*/
The CROSS JOIN generates the following.
id Colors
----------- --------------------
1 Red
1 Red
2 Red,Green
2 Red,Green
3 Green, Yellow
3 Green, Yellow
4 Yellow,Red,White
4 Yellow,Red,White
5 Green, White
5 Green, White
6 White
6 White
The WHERE filter then identifies the rows that has one of the values present in the XML result.
id Colors
----------- --------------------
1 Red
2 Red,Green
3 Green, Yellow
4 Yellow,Red,White
4 Yellow,Red,White
Finally, the DISTINCT clause removes duplicate rows generated by the CROSS JOIN and we get the final result.
id Colors
----------- --------------------
3 Green, Yellow
1 Red
2 Red,Green
4 Yellow,Red,White
One of my colleague needed a piece of code that returns all the dependent objects of a given database object. His project has a separate reporting server to which the data from primary database is replicated. He creates the database objects needed for the reports (stored procedures, functions, views etc) and then update the article list of the publication with the new objects so that they get replicated to the report server.
Every time he needs to deploy a new report or modify an existing report, he needs to know what are the database objects that depend on the given report stored procedure. Some times the reports are very complex and each stored procedure might have dozens of dependent objects and each dependent object may be depending on other dozens of objects.
He needed a way to recursively find all the depending objects of a given stored procedure. I wrote a recursive query using CTE to achieve this. Before we look at the query, lets us create some test data.
USE XMLWorkshop
GO
IF OBJECT_ID('depCustomers') IS NOT NULL
DROP TABLE depCustomers
GO
CREATE TABLE depCustomers (
DependsID INT,
CustomerName VARCHAR(40),
StatusID INT
)
GO
IF OBJECT_ID('GetCustomerStatus') IS NOT NULL
DROP FUNCTION GetCustomerStatus
GO
CREATE FUNCTION GetCustomerStatus
(
@StatusID INT
)
RETURNS VARCHAR(20)
AS
BEGIN
RETURN 'Active'
END
GO
IF OBJECT_ID('depCustomersView') IS NOT NULL
DROP VIEW depCustomersView
GO
CREATE VIEW depCustomersView
AS
SELECT
DependsID,
CustomerName,
StatusID
FROM depCustomers
GO
IF OBJECT_ID('depCustomersActive') IS NOT NULL
DROP VIEW depCustomersActive
GO
CREATE VIEW depCustomersActive
AS
SELECT
DependsID,
CustomerName,
StatusID,
dbo.GetCustomerStatus(StatusID) AS StatusText
FROM depCustomersView
WHERE StatusID = 1
GO
IF OBJECT_ID('depGetCustomerList') IS NOT NULL
DROP PROCEDURE depGetCustomerList
GO
CREATE PROCEDURE depGetCustomerList
AS
SELECT * FROM depCustomersActive
GO
Note that we have created a few objects that creates a hierarchy of dependencies. The procedure "depGetCustomerList" takes data from view "depCustomersActive" which depends on a function and another child view. Let us now write a function that builds this dependency relationship and returns the all the objects that depend on the stored procedure: "depGetCustomerList".
Here is the function:
CREATE FUNCTION GetDependents(
@ObjectName AS SYSNAME
)
RETURNS @result TABLE (
Seq INT IDENTITY,
ObjectName SYSNAME,
Hierarchy VARCHAR(128))
AS
BEGIN
;WITH Obj AS (
SELECT DISTINCT
s.id AS ParentID,
s.DepID AS ObjectID,
o1.Name AS ParentName,
o2.Name AS ChildName,
QUOTENAME(sch1.name) + '.' + QUOTENAME(o1.Name)
+ '(' + RTRIM(o1.type) + ')'
COLLATE SQL_Latin1_General_CP1_CI_AS
AS ParentObject,
QUOTENAME(sch2.name) + '.' + QUOTENAME(o2.Name)
+ '(' + RTRIM(o2.type) + ')'
COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName
FROM sys.sysdepends s
INNER JOIN sys.all_objects o1 ON s.id = o1.object_id
INNER JOIN sys.schemas sch1 ON sch1.schema_id = o1.schema_id
INNER JOIN sys.all_objects o2 on s.DepID = o2.object_id
INNER JOIN sys.schemas sch2 ON sch2.schema_id = o2.schema_id
), cte AS (
SELECT
0 AS lvl,
ParentID,
ObjectId,
ParentObject,
ObjectName,
CAST(ObjectID AS VARBINARY(512)) AS Sort
FROM obj WHERE ParentName = @ObjectName
UNION ALL
SELECT
p.lvl+ 1,
c.ParentID,
c.ObjectId,
c.ParentObject,
c.ObjectName,
CAST(p.sort + CAST(c.ObjectID AS VARBINARY(16))
AS VARBINARY(512))
FROM cte p
INNER JOIN obj c ON p.ObjectID = c.ParentID
)
INSERT INTO @result (ObjectName, Hierarchy)
SELECT
ObjectName,
'|-' + REPLICATE('-',(lvl * 4)) + ObjectName
FROM cte
ORDER BY Sort
RETURN
END
Now let us test this function. Run the following code to retrieve the dependency relationship we discussed earlier.
SELECT * FROM dbo.GetDependents('depGetCustomerList')
/*
Sr ObjectName Hierarchy
-- ----------------------------- -----------------------------------
1 [dbo].[depCustomersActive](V) |-[dbo].[depCustomersActive](V)
2 [dbo].[depCustomersView](V) |-----[dbo].[depCustomersView](V)
3 [dbo].[depCustomers](U) |---------[dbo].[depCustomers](U)
4 [dbo].[GetCustomerStatus](FN) |-----[dbo].[GetCustomerStatus](FN)
*/
In the previous post we discussed the logic we will apply to write the function that compares two XML values. We will write a function that accepts two XML variables and will compare elements and attributes as per the logic we discussed in the previous post.
I look forward to hear from you with alternate logic as well as suggestions to improve it. This function is not created keeping performance in mind. Instead, it is created to demonstrate a variety of XQuery methods and usages. The purpose of XQuery Lab is to demonstrate different XQuery usages and help people learn XQuery. May be, we will write an optimized version of this function later on.
Let us start writing the function. Here is the declaration of the function.
CREATE FUNCTION CompareXml
(
@xml1 XML,
@xml2 XML
)
RETURNS INT
AS
BEGIN
DECLARE @ret INT
SELECT @ret = 0
-- other code here
RETURN @ret
END
The function will return 1 if the values do not match and 0 if they matches. If one of the arguments is NULL we will return 1 to indicate that they are not equal. So "SELECT dbo.CompareXML(NULL, NULL)" will return 1.
IF @xml1 IS NULL OR @xml2 IS NULL BEGIN
RETURN 1
END
First of all, let us match the name of the root element of both XML values. If the names do not match, we will return 1.
IF (SELECT @xml1.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
<>
(SELECT @xml2.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
BEGIN
RETURN 1
END
Now, let us match the text/data each XML variable stores.
DECLARE @elValue1 VARCHAR(MAX), @elValue2 VARCHAR(MAX)
SELECT
@elValue1 = @xml1.value('((/*)[1])','VARCHAR(MAX)'),
@elValue2 = @xml2.value('data((/*)[1])','VARCHAR(MAX)')
IF @elValue1 <> @elValue2
BEGIN
RETURN 1
END
Next, let us match the number of attributes each root element has.
DECLARE @attCnt1 INT, @attCnt2 INT
SELECT
@attCnt1 = @xml1.query('count(/*/@*)').value('.','INT'),
@attCnt2 = @xml2.query('count(/*/@*)').value('.','INT')
IF @attCnt1 <> @attCnt2 BEGIN
RETURN 1
END
Now, let us create a loop that runs over all the attributes of the first XML value and matches the attributes (name and value) with the attributes of the second XML value. If the attribute is missing in the second XML value or the value is different, we will return 1.
DECLARE @cnt INT
DECLARE @attName VARCHAR(MAX)
DECLARE @attValue VARCHAR(MAX)
SELECT @cnt = 1
WHILE @cnt <= @attCnt1 BEGIN
SELECT @attName = NULL, @attValue = NULL
SELECT
@attName = @xml1.value('
local-name((/*/@*[sql:variable("@cnt")])[1])',
'varchar(MAX)'),
@attValue = @xml1.value('
(/*/@*[sql:variable("@cnt")])[1]',
'varchar(MAX)')
-- check if the attribute exists in the other XML document
IF @xml2.exist(
'(/*/@*[local-name()=sql:variable("@attName")])[1]'
) = 0
BEGIN
RETURN 1
END
IF @xml2.value(
'(/*/@*[local-name()=sql:variable("@attName")])[1]',
'varchar(MAX)'
)
<>
@attValue
BEGIN
RETURN 1
END
SELECT @cnt = @cnt + 1
END
If the operation succeeds so far, let us move ahead and validate the child elements. First of all, let us match the number of child elements each XML value has.
DECLARE @elCnt1 INT, @elCnt2 INT
SELECT
@elCnt1 = @xml1.query('count(/*/*)').value('.','INT'),
@elCnt2 = @xml2.query('count(/*/*)').value('.','INT')
IF @elCnt1 <> @elCnt2
BEGIN
RETURN 1
END
If the count of child elements matches, let us process each child element recursively. We will create a loop that runs over all the child elements and recursively call the "CompareXml" function.
SELECT @cnt = 1
DECLARE @x1 XML, @x2 XML
WHILE @cnt <= @elCnt1 BEGIN
SELECT
@x1 = @xml1.query('/*/*[sql:variable("@cnt")]'),
@x2 = @xml2.query('/*/*[sql:variable("@cnt")]')
IF dbo.CompareXml( @x1, @x2 ) = 1
BEGIN
RETURN 1
END
SELECT @cnt = @cnt + 1
END
Complete Source Listing
Here is the complete listing of the function.
CREATE FUNCTION CompareXml
(
@xml1 XML,
@xml2 XML
)
RETURNS INT
AS
BEGIN
DECLARE @ret INT
SELECT @ret = 0
-- -------------------------------------------------------------
-- If one of the arguments is NULl then we assume that they are
-- not equal.
-- -------------------------------------------------------------
IF @xml1 IS NULL OR @xml2 IS NULL BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Match the name of the elements
-- -------------------------------------------------------------
IF (SELECT @xml1.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
<>
(SELECT @xml2.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Match the value of the elements
-- -------------------------------------------------------------
DECLARE @elValue1 VARCHAR(MAX), @elValue2 VARCHAR(MAX)
SELECT
@elValue1 = @xml1.value('((/*)[1])','VARCHAR(MAX)'),
@elValue2 = @xml2.value('data((/*)[1])','VARCHAR(MAX)')
IF @elValue1 <> @elValue2
BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Match the number of attributes
-- -------------------------------------------------------------
DECLARE @attCnt1 INT, @attCnt2 INT
SELECT
@attCnt1 = @xml1.query('count(/*/@*)').value('.','INT'),
@attCnt2 = @xml2.query('count(/*/@*)').value('.','INT')
IF @attCnt1 <> @attCnt2 BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Match the attributes of attributes
-- Here we need to run a loop over each attribute in the
-- first XML element and see if the same attribut exists
-- in the second element. If the attribute exists, we
-- need to check if the value is the same.
-- -------------------------------------------------------------
DECLARE @cnt INT
DECLARE @attName VARCHAR(MAX)
DECLARE @attValue VARCHAR(MAX)
SELECT @cnt = 1
WHILE @cnt <= @attCnt1 BEGIN
SELECT @attName = NULL, @attValue = NULL
SELECT
@attName = @xml1.value(
'local-name((/*/@*[sql:variable("@cnt")])[1])',
'varchar(MAX)'),
@attValue = @xml1.value(
'(/*/@*[sql:variable("@cnt")])[1]',
'varchar(MAX)')
-- check if the attribute exists in the other XML document
IF @xml2.exist(
'(/*/@*[local-name()=sql:variable("@attName")])[1]'
) = 0
BEGIN
RETURN 1
END
IF @xml2.value(
'(/*/@*[local-name()=sql:variable("@attName")])[1]',
'varchar(MAX)')
<>
@attValue
BEGIN
RETURN 1
END
SELECT @cnt = @cnt + 1
END
-- -------------------------------------------------------------
-- Match the number of child elements
-- -------------------------------------------------------------
DECLARE @elCnt1 INT, @elCnt2 INT
SELECT
@elCnt1 = @xml1.query('count(/*/*)').value('.','INT'),
@elCnt2 = @xml2.query('count(/*/*)').value('.','INT')
IF @elCnt1 <> @elCnt2
BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Start recursion for each child element
-- -------------------------------------------------------------
SELECT @cnt = 1
DECLARE @x1 XML, @x2 XML
WHILE @cnt <= @elCnt1 BEGIN
SELECT
@x1 = @xml1.query('/*/*[sql:variable("@cnt")]'),
@x2 = @xml2.query('/*/*[sql:variable("@cnt")]')
IF dbo.CompareXml( @x1, @x2 ) = 1
BEGIN
RETURN 1
END
SELECT @cnt = @cnt + 1
END
RETURN @ret
END
Using the function
Let us test the function.
DECLARE @x1 XML, @x2 XML
SELECT @x1= '
<Employees>
<Employee FirstName="Jacob" LastName="Sebastian" />
</Employees>'
SELECT @x2= '
<Employees>
<Employee LastName="Sebastian" FirstName="Jacob"/>
<Employee LastName="Sebastian" FirstName="Jacob"/>
</Employees>'
SELECT dbo.CompareXml(@x1, @x2) AS Result
/*
Result
-----------
1
*/
SELECT @x1= '
<Employees>
<Employee FirstName="Jacob" LastName="Sebastian" />
</Employees>'
SELECT @x2= '
<Employees>
<Employee LastName="Sebastian" FirstName="Jacob"/>
</Employees>'
SELECT dbo.CompareXml(@x1, @x2) AS Result
/*
Result
-----------
0
*/
What next?
The current version of the function does not work with XML instances having namespace declarations. I am trying to improve this function and trying to take care of stuff that are missing in this version. I would like to hear the following from people who use this.
- Problems you faced
- Your suggestions
- Cases where the function does not produce expected results
- Anything else that you would like to share.
In the previous lab, we saw how to write a loop to process all the attributes of an element. We then discussed how to find the number of child elements that a parent element has. We also discussed how to retrieve a child element at the specified position. That is all we need to write a piece of code that runs a loop over all the child elements of an XML value.
Here is the XML that we will use for the example presented in this lab.
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>
We will write a loop that does not do anything interesting, but just print the child element to the output window. This might give you enough ideas to write the kind of loop that you need in your specific case.
-- XML instance
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
DECLARE
@cnt INT,
@totCnt INT,
@child XML
-- counter variables
SELECT
@cnt = 1,
@totCnt = @x1.value('count(/Employees/Employee)','INT')
-- loop
WHILE @cnt <= @totCnt BEGIN
SELECT
@child = @x1.query('/Employees/Employee[position()=sql:variable("@cnt")]')
PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR)
PRINT 'Child element: ' + CAST(@child AS VARCHAR(100))
PRINT ''
-- incremet the counter variable
SELECT @cnt = @cnt + 1
END
/*
OUTPUT:
Processing Child Element: 1
Child element: <Employee Number="1001" Name="Jacob"/>
Processing Child Element: 2
Child element: <Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
*/
Some times you might need to retrieve the XML element at a given position. It may be because you are running a loop over all the elements of the XML document to perform some application specific operations or you want to pass those elements to another application/stored-procedure/function etc to perform some custom processing. It could also be that you need to access each element individually and do some actions.
The following example demonstrates how to retrieve the child element of an XML document, at the specified position.
-- XML instance
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.query('/Employees/Employee[1]')
/*
OUTPUT:
<Employee Number="1001" Name="Jacob" />
*/
SELECT
@x1.query('/Employees/Employee[2]')
/*
OUTPUT:
<Employee Number="1002" Name="Bob" ReportsTo="Steve" />
*/
The following example shows how to use a variable to specify the position of the element needed.
-- XML instance
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
DECLARE @i INT
SELECT @i = 2
SELECT
@x1.query('/Employees/Employee[sql:variable("@i")]')
/*
OUTPUT:
<Employee Number="1002" Name="Bob" ReportsTo="Steve" />
*/
SELECT
@x1.query('/Employees/Employee[position()=sql:variable("@i")]')
/*
OUTPUT:
<Employee Number="1002" Name="Bob" ReportsTo="Steve" />
*/
This is one of the requirements we came across, while writing the TSQL function to compare two XML values. In one of the previous posts, we saw how to find the name and value of attributes at a specified location. We then saw how to find the number of attributes an element has.
So, we have discussed almost everything that we need to write a piece of code that loops through all the attributes in an element and performs some operations. The sample code given below runs a loop over all the attributes of an element prints the name and value of elements.
-- XML instance
DECLARE @x1 XML
SELECT @x1 = '<Employee Number="1001" Name="Jacob" Dept="IT"/>'
DECLARE
@cnt INT,
@totCnt INT,
@attName VARCHAR(30),
@attValue VARCHAR(30)
-- counter variables
SELECT
@cnt = 1,
@totCnt = @x1.value('count(/Employee/@*)','INT')
-- loop
WHILE @cnt <= @totCnt BEGIN
SELECT
@attName = @x1.value(
'local-name((/Employee/@*[position()=sql:variable("@cnt")])[1])',
'VARCHAR(30)'),
@attValue = @x1.value(
'(/Employee/@*[position()=sql:variable("@cnt")])[1]',
'VARCHAR(30)')
PRINT 'Attribute Position: ' + CAST(@cnt AS VARCHAR)
PRINT 'Attribute Name: ' + @attName
PRINT 'Attribute Value: ' + @attValue
PRINT ''
-- increment the counter variable
SELECT @cnt = @cnt + 1
END
/*
OUTPUT:
Attribute Position: 1
Attribute Name: Number
Attribute Value: 1001
Attribute Position: 2
Attribute Name: Name
Attribute Value: Jacob
Attribute Position: 3
Attribute Name: Dept
Attribute Value: IT
*/
You can check the existence of an attribute by using the "exist" method of the XML data type. The following example checks if the "Name" attribute exists in the "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '<Employee Number="1001" Name="Jacob"/>'
IF @x1.exist('/Employee/@Name') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
The following example shows how to check the existence of an attribute specified by a variable.
DECLARE @x1 XML
SELECT @x1 = '<Employee Number="1001" Name="Jacob"/>'
DECLARE @att VARCHAR(20)
SELECT @att = 'Number'
IF @x1.exist('/Employee/@*[local-name()=sql:variable("@att")]') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
In the previous post we saw how to find the value of an attribute at a specified position. In this post, we will see how to find the name of an attribute at a specified position. Here is the XML instance we will use for this lab.
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>
The following example retrieves the name of the first attribute of the first "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('local-name(
(/Employees/Employee[1]/@*[position()=1])[1]
)','VARCHAR(20)') AS AttName
/*
AttName
--------------------
Number
*/
The following example retrieves the name of the third attribute of the second "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('local-name(
(/Employees/Employee[2]/@*[position()=3])[1]
)','VARCHAR(20)') AS AttName
/*
AttName
--------------------
ReportsTo
*/
The following example shows how to read the name of an attribute at the position specified by a variable.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
DECLARE @pos INT
SELECT @pos = 2
SELECT
@x1.value('local-name(
(/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1]
)','VARCHAR(20)') AS AttName
/*
AttName
--------------------
Name
*/
It is easy to read the value if we know the name of the attribute. In this post we will examine how to read the value of an attribute at a given position. We will use the same XML instance we examined in the previous post.
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>
The following example retrieves the value of the first attribute from the first "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('(/Employees/Employee[1]/@*[position()=1])[1]','VARCHAR(20)') AS AttValue
/*
AttValue
--------------------
1001
*/
The following example retrieves the value of the third attribute from the second "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('(/Employees/Employee[2]/@*[position()=3])[1]','VARCHAR(20)') AS AttValue
/*
AttValue
--------------------
Steve
*/
The first time I came across this question is when I started writing the TSQL function to compare two XML values. Let us see how to count the attributes an element has. Here is the XML value that we will use for the examples in this lab.
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>
The root element "Employees" has only one attribute. The first "Employee" element has two attributes and the second employee element has 3 attributes. Let us see how to retrieve these values using XQuery.
The following query finds the attribute count of the root element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('count(/Employees/@*)','INT') AS AttributeCount
/*
AttributeCount
--------------
1
*/
The next query counts the attributes of the first "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('count(/Employees/Employee[1]/@*)','INT') AS AttributeCount
/*
AttributeCount
--------------
2
*/
The following query retrieves the attribute count of the second "Employee" element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('count(/Employees/Employee[2]/@*)','INT') AS AttributeCount
/*
AttributeCount
--------------
3
*/
If you do not know the name of elements, you can use a wildcard expression.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
@x1.value('count(/*/@*)','INT') AS Attributes_root,
@x1.value('count(/*/*[1]/@*)','INT') AS Attributes_Child1,
@x1.value('count(/*/*[2]/@*)','INT') AS Attributes_Child2
/*
Attributes_root Attributes_Child1 Attributes_Child2
--------------- ----------------- -----------------
1 2 3
*/
The following query retrieves the count of attributes in each Employee element.
DECLARE @x1 XML
SELECT @x1 = '
<Employees Dept="IT">
<Employee Number="1001" Name="Jacob"/>
<Employee Number="1002" Name="Bob" ReportsTo="Steve"/>
</Employees>'
SELECT
x.value('count(./@*)','INT') AS AttributeCount
FROM @x1.nodes('/*/*') y(x)
/*
Attributes_Child1
-----------------
2
3
*/
Well, this is yet another question we need to answer before we could write the TSQL function to compare two XML values. Let us look at an XML instance.
<Employees>
<Employee>
<Name>Jacob</Name>
<Number>1001</Number>
</Employee>
<Employee>
<Name>Bob</Name>
<Number>1002</Number>
</Employee>
<Employee>
<Name>Steve</Name>
<Number>1003</Number>
</Employee>
</Employees>
Note that <Employees> has 3 child elements and each <Employee> element has 2 child elements each. Let us write an XQuery that returns this information. The following query finds the number of child elements that the Employees element has.
DECLARE @x1 XML
SELECT @x1 = '
<Employees>
<Employee>
<Name>Jacob</Name>
<Number>1001</Number>
</Employee>
<Employee>
<Name>Bob</Name>
<Number>1002</Number>
</Employee>
<Employee>
<Name>Steve</Name>
<Number>1003</Number>
</Employee>
</Employees>'
SELECT
@x1.value('count(/Employees/Employee)','INT') AS Children
/*
Children
-----------
3
*/
The next example finds the number of child elements that the first Employee element has. We used a wildcard, because we want to count all the elements.
DECLARE @x1 XML
SELECT @x1 = '
<Employees>
<Employee>
<Name>Jacob</Name>
<Number>1001</Number>
</Employee>
<Employee>
<Name>Bob</Name>
<Number>1002</Number>
</Employee>
<Employee>
<Name>Steve</Name>
<Number>1003</Number>
</Employee>
</Employees>'
SELECT
@x1.value('count(/Employees/Employee[1]/*)','INT') AS Children
/*
Children
-----------
2
*/
If we do not know the name of the elements, we can use a wildcard to represent the elements at a given position on the hierarchy. Here is an example:
DECLARE @x1 XML
SELECT @x1 = '
<Employees>
<Employee>
<Name>Jacob</Name>
<Number>1001</Number>
</Employee>
<Employee>
<Name>Bob</Name>
<Number>1002</Number>
</Employee>
<Employee>
<Name>Steve</Name>
<Number>1003</Number>
</Employee>
</Employees>'
SELECT
@x1.value('count(/*/*)','INT') AS ChildrenOfRootElement,
@x1.value('count(/*/*[1]/*)','INT') AS ChildrenOfFirstChildElement
/*
ChildrenOfRootElement ChildrenOfFirstChildElement
--------------------- ---------------------------
3 2
*/
Most of the times we need only to read values from elements. However, there may be times when you need to read the name of elements as well. We came across this requirement when we started writing the TSQL function that compares two XML values, in the previous post.
The following example shows how to read the name of the root element of an XML value.
DECLARE @x XML
SELECT @x = '
<Employee>
<Number>1001</Number>
<Name>Jacob</Name>
</Employee>'
SELECT
@x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName
/*
ElementName
--------------------
Employee
*/
The next example shows how to read the name and value of the first element under the root element.
DECLARE @x XML
SELECT @x = '
<Employee>
<Number>1001</Number>
<Name>Jacob</Name>
</Employee>'
SELECT
@x.value('local-name((/*/*)[1])','VARCHAR(20)') AS ElementName,
@x.value('(/*/*/text())[1]','VARCHAR(20)') AS ElementValue
/*
ElementName ElementValue
-------------------- --------------------
Number 1001
*/
The next example shows how to read the name and value of the second element under the root element.
DECLARE @x XML
SELECT @x = '
<Employee>
<Number>1001</Number>
<Name>Jacob</Name>
</Employee>'
SELECT
@x.value('local-name((/*/*)[2])','VARCHAR(20)') AS ElementName,
@x.value('(/*/*/text())[2]','VARCHAR(20)') AS ElementValue
/*
ElementName ElementValue
-------------------- --------------------
Name Jacob
*/
The next example shows how to read the names and values of all the elements under the root element.
DECLARE @x XML
SELECT @x = '
<Employee>
<Number>1001</Number>
<Name>Jacob</Name>
</Employee>'
SELECT
x.value('local-name(.)','VARCHAR(20)') AS ElementName,
x.value('.','VARCHAR(20)') AS ElementValue
FROM @x.nodes('/*/*') y(x)
/*
ElementName ElementValue
-------------------- --------------------
Number 1001
Name Jacob
*/
More Posts
Next page »