August 2008 - Posts
We have seen the usage of XQuery function "local-name()" in one of the previous posts. This function is very helpful when you don't know the element or attribute to process. The calling process can specify an element or attribute name in a variable and you can perform an operation (select/insert/update) on the specified element or attribute by using this function.
Here is the XML document we need to process in this lab.
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<LastName>Sebastian</LastName>
</Employee>
<Employee>
<FirstName>Mike</FirstName>
<LastName>Jones</LastName>
</Employee>
</Employees>
We need to read the value of an element specified by a variable. The variable contains the name of the element to be retrieved. It could be FirstName or LastName. The following example shows how to use local-name() function to achieve this.
DECLARE @xml XML
set @xml = '
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<LastName>Sebastian</LastName>
</Employee>
<Employee>
<FirstName>Mike</FirstName>
<LastName>Jones</LastName>
</Employee>
</Employees>'
DECLARE @ElementName VARCHAR(20)
SELECT @ElementName = 'FirstName'
select x.value('.', 'varchar(20)') AS Name
FROM @xml.nodes('/Employees/Employee/*[local-name()=sql:variable("@ElementName")]') n(x)
/*
Name
--------------------
Jacob
Mike
*/
When you generate an XML document using FOR XML, columns having NULL value will be eliminated. Some times you might need to have an empty element (even if there is no value) generated to make sure that the application that processes the XML document does not complain. The following query shows an example that uses XSINIL instruction which generates empty elements for columns having NULL values.
DECLARE @t TABLE (
id INT, Name1 VARCHAR(20),
Value1 VARCHAR(20), Name2 VARCHAR(20),
Value2 VARCHAR(20))
INSERT INTO @t (id, name1, value1, name2, value2)
SELECT 1, 'PrimaryID', NULL, 'LastName', 'Abiola' UNION ALL
SELECT 2, 'PrimaryID', '200', 'LastName', 'Aboud'
SELECT
(
SELECT
name1 AS 'Parameter/Name',
value1 AS 'Parameter/Value'
FROM @t t2 WHERE t2.id = t.id
FOR XML PATH(''), ELEMENTS XSINIL, TYPE
),
(
SELECT
name2 AS 'Parameter/Name',
value2 AS 'Parameter/Value'
FROM @t t2 WHERE t2.id = t.id
FOR XML PATH(''), TYPE
)
FROM @t t
FOR XML PATH('T2Method')
<T2Method>
<Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>PrimaryID</Name>
<Value xsi:nil="true" />
</Parameter>
<Parameter>
<Name>LastName</Name>
<Value>Abiola</Value>
</Parameter>
</T2Method>
<T2Method>
<Parameter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Name>PrimaryID</Name>
<Value>200</Value>
</Parameter>
<Parameter>
<Name>LastName</Name>
<Value>Aboud</Value>
</Parameter>
</T2Method>
Note the "value" element in the first "paremeter" element. The value is NULL and still an element is generated. Note the addition of a special attribute "xsi:nil" to indicate that the element is empty. Note also the new namespace added without your consent :-)
FOR XML Tutorials
I wrote this query a few months back to help some one in the XML forums. Here is the sample XML data for this lab.
<Names>
<Name>Jacob</Name>
<Name>Steve</Name>
<Name>Bob</Name>
</Names>
The task is to read the value from the <name> elements. Well that is pretty simple. The tough part is that, we need to retrieve the position of each element too. We need to retrieve 1 for Jacob, 2 for Steve and 3 for Bob.
XQuery does not provide a function to retrieve this information. The "position()" function cannot be used in the value() method. Hence I wrote the following query, which is little funny. It joins the XML nodes with a system table spt_values which contains a sequence of numbers. The number column is joined with the position() of each element and that gives us the position of each element. Here is the query:
DECLARE @x XML
SELECT @x = '
<Names>
<Name>Jacob</Name>
<Name>Steve</Name>
<Name>Bob</Name>
</Names>'
SELECT
p.number as Position,
x.value('.','VARCHAR(10)') AS Name
FROM
master..spt_values p
cross APPLY @x.nodes('/Names/Name[position()=sql:column("number")]') n(x)
where p.type = 'p'
/*
Position Name
----------- ----------
1 Jacob
2 Steve
3 Bob
*/
In lab 20 we saw how to move an element up or down. In lab 21 we saw how to move an element to a location relative to another element having a specific value. In this lab, let us see how to move elements to specific positions. Here is the sample data for this lab.
<Employees>
<Employee Name="Jacob"/>
<Employee Name="Steve"/>
<Employee Name="Bob"/>
<Employee Name="Mike"/>
</Employees>
Let us move Jacob to the 3rd position. Then let us move Steve as the last element.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Name="Jacob"/>
<Employee Name="Steve"/>
<Employee Name="Bob"/>
<Employee Name="Mike"/>
</Employees>'
------------------------------------------------------------
-- Move "Jacob" to position 3
------------------------------------------------------------
set @x.modify('
insert /Employees/Employee[@Name="Jacob"]
after (/Employees/Employee)[3]
')
SET @x.modify ('
delete (/Employees/Employee[@Name="Jacob"])[1]
')
SELECT @x
/*
<Employees>
<Employee Name="Steve" />
<Employee Name="Bob" />
<Employee Name="Jacob" />
<Employee Name="Mike" />
</Employees>
*/
------------------------------------------------------------
-- Move "Steve" to the last position
------------------------------------------------------------
set @x.modify('
insert /Employees/Employee[@Name="Steve"]
after (/Employees/Employee)[last()]
')
SET @x.modify ('
delete (/Employees/Employee[@Name="Steve"])[1]
')
SELECT @x
/*
<Employees>
<Employee Name="Bob" />
<Employee Name="Jacob" />
<Employee Name="Mike" />
<Employee Name="Steve" />
</Employees>
*/
In the previous lab, we saw how to move an element up or down. Now let us see how to move an element to a specific location relative to other elements. Here is the sample data for this lab.
<Employees>
<Employee Name="Jacob"/>
<Employee Name="Steve"/>
<Employee Name="Bob"/>
<Employee Name="Mike"/>
</Employees>
Now let us move Bob before Jacob and move Jacob after Mike.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Name="Jacob"/>
<Employee Name="Steve"/>
<Employee Name="Bob"/>
<Employee Name="Mike"/>
</Employees>'
------------------------------------------------------------
-- Move "Bob" before "Jacob"
------------------------------------------------------------
set @x.modify('
insert /Employees/Employee[@Name="Bob"]
before (/Employees/Employee[@Name="Jacob"])[1]
')
SET @x.modify ('
delete (/Employees/Employee[@Name="Bob"])[2]
')
SELECT @x
/*
<Employees>
<Employee Name="Bob" />
<Employee Name="Jacob" />
<Employee Name="Steve" />
<Employee Name="Mike" />
</Employees>
*/
------------------------------------------------------------
-- Move "Jacob" after "Mike"
------------------------------------------------------------
set @x.modify('
insert /Employees/Employee[@Name="Jacob"]
after (/Employees/Employee[@Name="Mike"])[1]
')
SET @x.modify ('
delete (/Employees/Employee[@Name="Jacob"])[1]
')
SELECT @x
/*
<Employees>
<Employee Name="Bob" />
<Employee Name="Steve" />
<Employee Name="Mike" />
<Employee Name="Jacob" />
</Employees>
*/
Position of elements is significant in XML. The following example shows how to move an element up or down within a parent element. Here is the sample data for this lab.
<Employees>
<Employee Name="Jacob"/>
<Employee Name="Steve"/>
<Employee Name="Bob"/>
</Employees>
Now let us try to move elements up and down. Let us move Bob one level up and move Jacob one level down.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee Name="Jacob"/>
<Employee Name="Steve"/>
<Employee Name="Bob"/>
</Employees>'
------------------------------------------------------------
-- Move "Bob" one level up
------------------------------------------------------------
set @x.modify('
insert /Employees/Employee[@Name="Bob"]
before (/Employees/Employee[. << (/Employees/Employee[@Name="Bob"])[1]])
[last()]
')
SET @x.modify ('
delete /Employees/Employee[@Name="Bob"]
[. is (/Employees/Employee[@Name="Bob"])[last()]]
')
SELECT @x
/*
<Employees>
<Employee Name="Jacob" />
<Employee Name="Bob" />
<Employee Name="Steve" />
</Employees>
*/
------------------------------------------------------------
-- Move "Jacob" one level down
------------------------------------------------------------
set @x.modify('
insert /Employees/Employee[@Name="Jacob"]
before (/Employees/Employee[. >> (/Employees/Employee[@Name="Bob"])[1]])
[last()]
')
SET @x.modify ('
delete (/Employees/Employee[@Name="Jacob"])[1]
')
SELECT @x
/*
<Employees>
<Employee Name="Bob" />
<Employee Name="Jacob" />
<Employee Name="Steve" />
</Employees>
*/
One of the two common string operations that we do often are 'parsing' delimited strings and 'generating' delimited strings. This post explains how to generate a delimited string using FOR XML.
I like XML and many of you might have noted the reflection of this likeness in my posts. When I try to solve a problem, I usually look for an XML based approach before trying any other method. This does not mean that the XML approach is always superior. There are times when it is good and there are times when an XML approach is not desirable.
I have experienced that TSQL loops are very expensive (usually). So most of the times, if you can re-write a loop to a batch/set operation, you could get performance benefits (well, most of the times. There are times when this may not be true, but such cases are very rare).
There are two common string operations where I used to write a TSQL loop in the SQL server 2000 era.
- To split a delimited string and return a set
- To generate a delimited string from a set
The XML enhancements added to SQL Server 2005 made both these operations easier with XML. I think, most of the times these operations are done in small pieces of data. Though you can do these operations on extremely large data, I don't think it is advisable. There are other ways to handle large chunks of data.
In this post, lets see how we could generate a delimited string using FOR XML PATH. I have covered the other topic "How to split a delimited string" in another post.
This post is inspired by a discussion with a colleague. Here is the details of the specific requirement. Let us first see the source data.
DECLARE @companies Table(
CompanyID INT,
CompanyCode int
)
insert into @companies(CompanyID, CompanyCode) values(1,1)
insert into @companies(CompanyID, CompanyCode) values(1,2)
insert into @companies(CompanyID, CompanyCode) values(2,1)
insert into @companies(CompanyID, CompanyCode) values(2,2)
insert into @companies(CompanyID, CompanyCode) values(2,3)
insert into @companies(CompanyID, CompanyCode) values(2,4)
insert into @companies(CompanyID, CompanyCode) values(3,1)
insert into @companies(CompanyID, CompanyCode) values(3,2)
SELECT * FROM @companies
/*
CompanyID CompanyCode
----------- -----------
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
*/
This is the result that we need.
/*
CompanyID CompanyString
----------- -------------------------
1 1|2
2 1|2|3|4
3 1|2
*/
One option is to run a loop that constructs a delimited string for each CompanyID. Another option is to create a function that returns a delimited string for each company ID. I am presenting a third option using FOR XML PATH.
SELECT CompanyID,
(SELECT
CompanyCode AS 'data()'
FROM @companies c2
WHERE c2.CompanyID = c1.CompanyID
FOR XML PATH('')) AS CompanyString
FROM @companies c1
GROUP BY CompanyID/*
CompanyID CompanyString
----------- ------------------------
1 1 2
2 1 2 3 4
3 1 2
*/
The above query uses FOR XML PATH to return a SPACE delimited string containing the company code of each row. But this is not the final result that we need. We need a pipe separated list and hence we need to apply a REPLACE() operation.
SELECT CompanyID,
REPLACE((SELECT
CompanyCode AS 'data()'
FROM @companies c2
WHERE c2.CompanyID = c1.CompanyID
FOR XML PATH('')), ' ', '|') AS CompanyString
FROM @companies c1
GROUP BY CompanyID
/*
CompanyID CompanyString
----------- -------------------------
1 1|2
2 1|2|3|4
3 1|2
*/
XML Data Type functions can be used to perform a number of string operations. This post explains how to parse a delimited string using the XML approach. The XML approach helps you to avoid the WHILE-LOOP that you might need with most other approaches.
In one of my previous posts, I had presented an example which generated a delimited string using FOR XML PATH. You can read the post here. In this post, let us take the results of that lab and see if we can revert back to the input of that lab by splitting the delimited values back. This was our source data.
/*
CompanyID CompanyCode
----------- -----------
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
*/
Then we generated a delimited string using FOR XML PATH and here is the output.
/*
CompanyID CompanyString
----------- -------------------------
1 1|2
2 1|2|3|4
3 1|2
*/
Now let us take this output and see if we can split it back and get the original values.
DECLARE @companies Table(
CompanyID INT,
CompanyCodes VARCHAR(100)
)
insert into @companies(CompanyID, CompanyCodes) values(1,'1|2')
insert into @companies(CompanyID, CompanyCodes) values(2,'1|2|3|4')
insert into @companies(CompanyID, CompanyCodes) values(3,'1|2')
SELECT * FROM @companies
/*
CompanyID CompanyCodes
----------- -----------------------
1 1|2
2 1|2|3|4
3 1|2
*/
Here is the query that returns the desired results.
-- create table
DECLARE @companies Table(
CompanyID INT,
CompanyCodes VARCHAR(100)
)
-- insert data
insert into @companies(CompanyID, CompanyCodes) values(1,'1|2')
insert into @companies(CompanyID, CompanyCodes) values(2,'1|2|3|4')
insert into @companies(CompanyID, CompanyCodes) values(3,'1|2')
-- Query
;WITH cte AS (
SELECT
CompanyID,
CAST('<i>' + REPLACE(CompanyCodes, '|', '</i><i>') + '</i>' AS XML) AS CompanyCodes
FROM @Companies
)
SELECT
CompanyID,
x.i.value('.', 'VARCHAR(10)') AS CompanyCode
FROM cte
CROSS APPLY CompanyCodes.nodes('//i') x(i)
/*
CompanyID CompanyCode
----------- -----------
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
*/
The CTE returns a result set which transforms the delimited string to a well-formed XML fragment. The next query uses XQuery value() method to retrieve the values of the nodes.
We had seen an example that demonstrated how to delete an attribute from an XML variable. Let us now try to delete an element from an XML document/variable. Here is the sample XML document for this lab.
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<MiddleName>V</MiddleName>
<LastName>Sebastian</LastName>
</Employee>
</Employees>
Let us try to delete the MiddleName element. Here is the code that does this.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<MiddleName>V</MiddleName>
<LastName>Sebastian</LastName>
</Employee>
</Employees>'
DECLARE @MiddleName CHAR(1)
SELECT @MiddleName = 'J'
SET @x.modify('
delete (/Employees/Employee/MiddleName)[1]'
)
SELECT @x
/*
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<LastName>Sebastian</LastName>
</Employee>
</Employees>
*/
I had recently started a series of blog posts on XQuery which presented a series of short examples demonstrating various XQuery features. The goal of the series is to present a set of commonly used examples that explain the usage of XQuery in SQL Server.
While the XQuery Labs is still progressing, I would like to start another series on SQLXML. We will start with XMLBulkLoad and move on to other components. If you have an SQLXML question, please send me a note and I will try to answer it. This post will serve as an index page that can take you to other posts in this series. I will update this page from time to time to make sure that all the posts in this series are listed here. You could also locate the posts in this series by clicking on the SQLXML label on the right control panel.
Here is the list of posts in this series.
- SQLXML Lab 1 - Introduction to XML Bulk Load
- SQLXML Lab 2 - A basic XML Bulk Load Example
- SQLXML Lab 3 - Loading information from elements
- SQLXML Lab 4 - Loading information to multiple unrelated tables
In the previous labs, we have seen several examples of inserting/updating/deleting attributes. The syntax for updating the values of elements is slightly different from that of attributes. Let us see an example to understand this.
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<MiddleName>V</MiddleName>
<LastName>Sebastian</LastName>
</Employee>
</Employees>
This is the sample XML. We need to update the MiddleName with a different value. Here is the code that does it.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<MiddleName>V</MiddleName>
<LastName>Sebastian</LastName>
</Employee>
</Employees>'
DECLARE @MiddleName CHAR(1)
SELECT @MiddleName = 'J'
SET @x.modify('
replace value of (/Employees/Employee/MiddleName/text())[1]
with sql:variable("@MiddleName")' )
SELECT @x
/*
<Employees>
<Employee>
<FirstName>Jacob</FirstName>
<MiddleName>J</MiddleName>
<LastName>Sebastian</LastName>
</Employee>
</Employees>
*/
In Lab 15, we saw how to modify an attribute based on the value of a variable. We used the value of a variable to locate the correct element. We located the element having a specific attribute with the value specified in a variable.
Now let us look at a bit more complex example. Assume a case where we don't know which attribute to modify. The attribute to be modified is passed to our code as a parameter. So we have a string parameter that stores the name of the attribute to be modified. Let us look at an example.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee FirstName="Jacob" MiddleName="V" LastName="Sebastian"/>
</Employees>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'MiddleName'
SELECT @val = 'J'
The task to modify the attribute specified by variable @var and replace the value with the value specified by variable @v. After we update, the attribute "MiddleName" should be replaced with value "J". Let us see the code.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee FirstName="Jacob" MiddleName="V" LastName="Sebastian"/>
</Employees>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'MiddleName'
SELECT @val = 'J'
SET @x.modify('
replace value of (
/Employees/Employee/@*[local-name()=sql:variable("@var")]
)[1]
with sql:variable("@val")
')
select @x
/*
<Employees>
<Employee FirstName="Jacob" MiddleName="J" LastName="Sebastian" />
</Employees>
*/
Earlier in XQuery lab, we have seen how to modify the value of an attribute. So it is easy when we know which element to update and which attribute to modify. Let us look at a slightly different example. Assume that we need to modify a given attribute of a node. We need to identify the correct node based on the value of another attribute. The value of this attribute is passed as a parameter and we need to locate a node that has an attribute with a specific value stored in the variable. Let us look at an example.
DECLARE @x XML
SELECT @x = '
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="3" />
</Variables>
</Root>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'V3'
SELECT @val = '6'
The variable @var contains value "V3". We need to locate the XML node which is having value "V3" in the "VariableName" attribute and update the value of the "Value" attribute with the value specified by variable "@val". This should update the 3rd row and replace the value of "value" attribute to "6". Let us see the code that demonstrates this.
DECLARE @x XML
SELECT @x = '
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="3" />
</Variables>
</Root>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'V3'
SELECT @val = '6'
SET @x.modify('
replace value of (
/Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value
)[1]
with sql:variable("@val")
')
SELECT @x
/*
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="6" />
</Variables>
</Root>
*/
The example we saw above used an XML variable. Let us now see how to perform the same operation in an XML column.
DECLARE @t TABLE (data XML)
INSERT INTO @t (data) SELECT '
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="3" />
</Variables>
</Root>'
DECLARE @var VARCHAR(20)
DECLARE @val VARCHAR(20)
SELECT @var = 'V3'
SELECT @val = '6'
UPDATE @t
SET data.modify('
replace value of (/Root/Variables/Variable[@VariableName=sql:variable("@var")]/@Value)[1]
with sql:variable("@val")
')
SELECT * FROM @t
/*
<Root>
<Variables>
<Variable VariableName="V1" Value="1" />
<Variable VariableName="V2" Value="2" />
<Variable VariableName="V3" Value="6" />
</Variables>
</Root>
*/
In one of the previous labs, we saw an example that select specific nodes by applying a filter on the value of an attribute. Some times, it can happen that you need to apply filters on more than one attribute to retrieve a set of nodes matching a given criteria. This post shows an example that applies filters on 2 attributes.
Here is the sample data.
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>
Assume that we need to run two queries on this XML. The first query should return all employees from IT department. This should return two rows. The second query should return all the permanent employees from IT department. This should return only one row.
Let us write the first query. Let us apply a filter on the "dept" attribute.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>'
SELECT
e.value('@first[1]','VARCHAR(10)') AS FirstName,
e.value('@middle[1]','VARCHAR(10)') AS MiddleName,
e.value('@last[1]','VARCHAR(10)') AS LastName
FROM @x.nodes('/Employees/Employee[@dept="IT"]/Name') x(e)
/*
FirstName MiddleName LastName
---------- ---------- ----------
Jacob V Sebastian
Steve K Austine
*/
The following query also produces the same result. Note the changes in in the expression used with the "nodes()" method.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>'
SELECT
e.value('@first[1]','VARCHAR(10)') AS FirstName,
e.value('@middle[1]','VARCHAR(10)') AS MiddleName,
e.value('@last[1]','VARCHAR(10)') AS LastName
FROM @x.nodes('//Employee[@dept="IT"]/*') x(e)
/*
FirstName MiddleName LastName
---------- ---------- ----------
Jacob V Sebastian
Steve K Austine
*/
Now let us write the second query. Let us add two filters: one on the "dept" attribute and the other on the "type" attribute.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="123" dept="IT" type="Permanent">
<Name first="Jacob" middle="V" last="Sebastian"/>
</Employee>
<Employee id="234" dept="IT" type="Temporary">
<Name first="Steve" middle="K" last="Austine"/>
</Employee>
<Employee id="345" dept="OP" type="Permanent">
<Name first="Smith" middle="R" last="Wills"/>
</Employee>
</Employees>'
SELECT
e.value('@first[1]','VARCHAR(10)') AS FirstName,
e.value('@middle[1]','VARCHAR(10)') AS MiddleName,
e.value('@last[1]','VARCHAR(10)') AS LastName
FROM @x.nodes('//Employee[@dept="IT"][@type="Permanent"]/*') x(e)
/*
FirstName MiddleName LastName
---------- ---------- ----------
Jacob V Sebastian
*/
When you write a simple FOR XML query with PATH, you will see that a <row> element will be generated for each row in the result set. For example:
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name FROM @t
FOR XML PATH, ROOT ('Employees')
/*
<Employees>
<row>
<Name>Jacob</Name>
</row>
<row>
<Name>Steve</Name>
</row>
</Employees>
*/
Note that a <node> element is created for each row in the query result. PATH is a very powerful operator that allows a great deal of flexibility. Most of the operations previously possible only with EXPLICIT is now possible with PATH. Let us see a few simple variations of the above query and see how we could control the format of the output.
Let us first of all, remove the <node> element.
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name FROM @t
FOR XML PATH(''), ROOT ('Employees')
/*
<Employees>
<Name>Jacob</Name>
<Name>Steve</Name>
</Employees>
*/
Now, Let us put each employee under an <Employee> Node.
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name FROM @t
FOR XML PATH('Employee'), ROOT ('Employees')
/*
<Employees>
<Employee>
<Name>Jacob</Name>
</Employee>
<Employee>
<Name>Steve</Name>
</Employee>
</Employees>
*/
Finally, lets us change the <Name> element to an attribute.
DECLARE @t TABLE (Name VARCHAR(10))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
SELECT Name AS '@Name' FROM @t
FOR XML PATH('Employee'), ROOT ('Employees')
/*
<Employees>
<Employee Name="Jacob" />
<Employee Name="Steve" />
</Employees>
*/
Is there a FOR XML query that you find hard to write? Send me a note and I will try to help you out.
FOR XML Tutorials
More Posts
Next page »