Working with Namespaces is little more tricky. The following code shows an example that queries values from an XML document, having namespace declaration. Note the usage of WITH NAMESPACES which resolves the namespace references. Note also the usage of OUTER APPLY which joins the parent nodes with the child nodes.
DECLARE @var XML
SET @var = '
<Customers xmlns="urnchemas-microsoft-comqlqlRowSet2">
<Customer>
<CompanyName>Wide World Importers</CompanyName>
<OrderLine>
<SKU>WWIR375</SKU>
<Quantity>1</Quantity>
</OrderLine>
<OrderLine>
<SKU>WWIRB1-2RO</SKU>
<Quantity>16</Quantity>
</OrderLine>
</Customer>
<Customer>
<CompanyName>US Importers</CompanyName>
<OrderLine>
<SKU>WWIR376</SKU>
<Quantity>1</Quantity>
</OrderLine>
<OrderLine>
<SKU>WWIRB1-3RO</SKU>
<Quantity>16</Quantity>
</OrderLine>
</Customer>
</Customers>'
;WITH XMLNAMESPACES(
DEFAULT 'urnchemas-microsoft-comqlqlRowSet2'
)
SELECT
x.value('CompanyName[1]','VARCHAR(20)') AS CompanyName,
y.value('SKU[1]','VARCHAR(10)') AS Sku,
y.value('Quantity[1]','INT') AS Quantity
FROM @var.nodes('/Customers/Customer') v(x)
OUTER APPLY x.nodes('OrderLine') o(y)
/*
CompanyName Sku Quantity
-------------------- ---------- -----------
Wide World Importers WWIR375 1
Wide World Importers WWIRB1-2RO 16
US Importers WWIR376 1
US Importers WWIRB1-3RO 16
*/
Recently some one asked me if we could join XML nodes with a relational table. The answer is YES. I wrote a quick sample that demonstrates this. Here is an example.
DECLARE @t TABLE (EmpID INT, EmpName VARCHAR(20))
INSERT INTO @t (EmpID, EmpName) SELECT 1, 'Jacob'
INSERT INTO @t (EmpID, EmpName) SELECT 2, 'Bob'
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="1" hireDate="somedate"/>
<Employee id="2" hireDate="otherDate"/>
</Employees>'
SELECT
t.EmpID,
t.EmpName,
x.value('@hireDate[1]','VARCHAR(10)') AS HireDate
FROM
@x.nodes('/Employees/Employee') e(x)
CROSS APPLY @t t
WHERE x.value('@id[1]','int') = t.EmpID
/*
EmpID EmpName HireDate
----------- -------------------- ----------
1 Jacob somedate
2 Bob otherDate
*/
The above example joins a memory table (@t) with an XML variable (@x) on the column EmpID and returns information. EmpName is stored in the table and HireDate is stored in the XML variable. The query joins them and returns the results.
Sometimes we need to apply a filter on the nodes of an XML document and retrieve only those nodes that meet a specific criteria. Most of the times, such filters will be applied on a given attribute value of the specific element. Here is such an example.
Here is the sample XML document that we have.
<root>
<Branch id="1">
<PeriodType pt="0">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>06/16/2008</StartDate>
<EndDate>06/22/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>06/09/2008</StartDate>
<EndDate>06/15/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>06/02/2008</StartDate>
<EndDate>06/08/2008</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="1">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
</PeriodType>
</Branch>
</root>
We need to write a query that reads the StartDate and EndDate of all <PeriodType> elements where the "pt" attribute has a value of "1". This filter can be applied directly within the "nodes()" operator of the XML data type. Here is the expected result.
/*
StartDate EndDate
----------------------- -----------------------
2008-06-23 00:00:00.000 2008-06-24 00:00:00.000
*/
The query should return only one row, because there is only one XML element that where the "pt" attribute of <PeriodType> is "1". Here is the query that performs this.
DECLARE @dates xml
SET @dates = '
<root>
<Branch id="1">
<PeriodType pt="0">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="2">
<StartDate>06/16/2008</StartDate>
<EndDate>06/22/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="3">
<StartDate>06/09/2008</StartDate>
<EndDate>06/15/2008</EndDate>
</PeriodNumber>
<PeriodNumber pn="4">
<StartDate>06/02/2008</StartDate>
<EndDate>06/08/2008</EndDate>
</PeriodNumber>
</PeriodType>
<PeriodType pt="1">
<PeriodNumber pn="1">
<StartDate>06/23/2008</StartDate>
<EndDate>06/24/2008</EndDate>
</PeriodNumber>
</PeriodType>
</Branch>
</root>'
SELECT
x.value('StartDate[1]','DATETIME') AS StartDate,
x.value('EndDate[1]','DATETIME') AS EndDate
FROM @dates.nodes('/root/Branch/PeriodType[@pt="1"]/PeriodNumber') d(x)
/*
StartDate EndDate
----------------------- -----------------------
2008-06-23 00:00:00.000 2008-06-24 00:00:00.000
*/
This is the first of a series of XQuery posts I plan to write under the ‘XQuery Labs’ series. The purpose of this series is to demonstrate the usages of XML Data type methods and XQuery functions.
Input
I have an XML document which looks like the following.
<QUOTE configID="arbu05_0X4fe77d8454141612">
<CSTICS>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_CONFIG" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_ELECTRICAL" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
</CSTICS>
</QUOTE>
Look at the child nodes of ORDER_CFGS_VALUE. Each ORDER_CFGS_VALUE element has 4 child nodes named field. We need to write a query that transforms those nodes into columns.
Expected output
The output should look like the following.
CONFIG_ID INST_ID CHARC VALUE
-------------------- ----------- -------------------- -----------
arbu05_0X4fe77d84541 2 A7J_ID_CONFIG 0
arbu05_0X4fe77d84541 2 A7J_ID_ELECTRICAL 0
The query should return only two rows; one row for each ORDER_CFGS_VALUE. The children of ORDER_CFGS_VALUE having name CONFIG_ID, INST_ID, CHARC and VALUE should be transformed as columns.
The Query
DECLARE @x XML
SELECT @x = '
<QUOTE configID="arbu05_0X4fe77d8454141612">
<CSTICS>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_CONFIG" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
<ORDER_CFGS_VALUE>
<Field Name="CONFIG_ID" Value="arbu05_0X4fe77d8454141612" />
<Field Name="INST_ID" Value="2" />
<Field Name="CHARC" Value="A7J_ID_ELECTRICAL" />
<Field Name="VALUE" Value="0" />
</ORDER_CFGS_VALUE>
</CSTICS>
</QUOTE>'
SELECT
x.value('(Field[@Name="CONFIG_ID"]/@Value)[1]','VARCHAR(20)') AS CONFIG_ID,
x.value('(Field[@Name="INST_ID"]/@Value)[1]','INT') AS INST_ID,
x.value('(Field[@Name="CHARC"]/@Value)[1]','VARCHAR(20)') AS CHARC,
x.value('(Field[@Name="VALUE"]/@Value)[1]','INT') AS VALUE
FROM @x.nodes('/QUOTE/CSTICS/ORDER_CFGS_VALUE') d(x)
/*
CONFIG_ID INST_ID CHARC VALUE
-------------------- ----------- -------------------- -----------
arbu05_0X4fe77d84541 2 A7J_ID_CONFIG 0
arbu05_0X4fe77d84541 2 A7J_ID_ELECTRICAL 0
*/
Note the way filters are applied on the value of attribute "Name". The expression "Field[@Name="CONFIG_ID"]" will take you to the XML element having "CONFIG_ID" as the value of the "Name" attribute. After accessing this element, the value of the "value" attribute is retrieved with the expression "Field[@Name="CONFIG_ID"]/@Value"
Comments/Questions are welcome!
XQuery Labs - A Collection of XQuery Sample Scripts
I have always experienced and am convinced that, while learning a programming technique, it is more helpful to see an example that matches our specific requirement, rather than some theoretical stuff. Examples are really good for learning the basics and then the theories will help us to go deeper into the specific technique.
SQL Server 2005 introduced XML data type and a few related technologies like XQuery, XSD etc. People having no previous experience with XQuery or XSD might find it little hard to find a starting point. Initially, this might seem little complex and then as you go ahead, things will get smoother.
I am trying to post a series of XQuery sample scripts. Most of these scripts were created to help people who needed help at various online forums. I guess some of you will find a few examples that closely matches with your specific requirement and will give enough hints to solve the problem you have at hand.
If you don't find an example that matches with your requirement, you could leave me a note and I will try to add it. Given below is the list of XQuery sample scripts that I have currently. I will keep this page updated when a new script is added. So probably it is a good idea to bookmark this page, if you want to keep a watch on the new scripts being posted. Alternatively, you could subscribe to the RSS/ATOM feeds available on this page and instantly get noticed about new posts as well as updated on existing posts.
XQuery Sample Scripts
This example has an XML document with a bit complex node structure. The XML document has 3 <Pupil> elements and each of those elements has several child elements. If you have a closer look into the XML structure you could see that, not all the child elements have same number of elements. Some of them are missing certain elements. The content of those 3 <Pupil> elements are not the same.
<Pupils>
<Pupil>
<PupilIdentifiers>
<PupilID>12345</PupilID>
<Surname>BOB</Surname>
<Forename>HOPE</Forename>
<Gender>M</Gender>
<DOB>1997-02-18</DOB>
</PupilIdentifiers>
<Attendance>
<TermlyAttendance>
<SessionsPossible>96</SessionsPossible>
<SessionDetails>
<SessionDetail>
<AttendanceReason>I</AttendanceReason>
<AbsenceSessions>6</AbsenceSessions>
</SessionDetail>
<SessionDetail>
<AttendanceReason>M</AttendanceReason>
<AbsenceSessions>1</AbsenceSessions>
</SessionDetail>
</SessionDetails>
</TermlyAttendance>
</Attendance>
</Pupil>
<Pupil>
<PupilIdentifiers>
<PupilID>87389373</PupilID>
<Surname>Shaun</Surname>
<Forename>Allcock</Forename>
<Gender>M</Gender>
<DOB>1997-02-18</DOB>
</PupilIdentifiers>
<Attendance>
<TermlyAttendance>
<SessionsPossible>109</SessionsPossible>
</TermlyAttendance>
</Attendance>
</Pupil>
<Pupil>
<PupilIdentifiers>
<PupilID>1234eqwe5</PupilID>
<Surname>BOBBY</Surname>
<Forename>HOPE</Forename>
<Gender>M</Gender>
<DOB>1997-02-18</DOB>
</PupilIdentifiers>
<Attendance>
<TermlyAttendance>
<SessionsPossible>89</SessionsPossible>
<SessionDetails>
<SessionDetail>
<AttendanceReason>S</AttendanceReason>
<AbsenceSessions>60</AbsenceSessions>
</SessionDetail>
<SessionDetail>
<AttendanceReason>X</AttendanceReason>
<AbsenceSessions>30</AbsenceSessions>
</SessionDetail>
</SessionDetails>
</TermlyAttendance>
</Attendance>
</Pupil>
</Pupils>
The following output needs to be generated from the above XML document.
/*
UPN Surname Forename SessionsPossible AttendanceReason AttendanceSessions
--------------- ---------- ---------- ---------------- ---------------- ------------------
12345 BOB HOPE 96 I 6
12345 BOB HOPE 96 M 1
87389373 Shaun Allcock 109 NULL NULL
1234eqwe5 BOBBY HOPE 89 S 60
1234eqwe5 BOBBY HOPE 89 X 30
*/
We need to generate a row for each <SessionDetail> element. That is not very hard. But the tricky part is that, the second <Pupil> element does not have a <SessionDetail> element, but still we need to display a row having Session information shown as NULL.
A query using OUTER APPLY can be written to achieve the results required in this lab.
DECLARE @x XML
SELECT @x = '
<Pupils>
<Pupil>
<PupilIdentifiers>
<PupilID>12345</PupilID>
<Surname>BOB</Surname>
<Forename>HOPE</Forename>
<Gender>M</Gender>
<DOB>1997-02-18</DOB>
</PupilIdentifiers>
<Attendance>
<TermlyAttendance>
<SessionsPossible>96</SessionsPossible>
<SessionDetails>
<SessionDetail>
<AttendanceReason>I</AttendanceReason>
<AbsenceSessions>6</AbsenceSessions>
</SessionDetail>
<SessionDetail>
<AttendanceReason>M</AttendanceReason>
<AbsenceSessions>1</AbsenceSessions>
</SessionDetail>
</SessionDetails>
</TermlyAttendance>
</Attendance>
</Pupil>
<Pupil>
<PupilIdentifiers>
<PupilID>87389373</PupilID>
<Surname>Shaun</Surname>
<Forename>Allcock</Forename>
<Gender>M</Gender>
<DOB>1997-02-18</DOB>
</PupilIdentifiers>
<Attendance>
<TermlyAttendance>
<SessionsPossible>109</SessionsPossible>
</TermlyAttendance>
</Attendance>
</Pupil>
<Pupil>
<PupilIdentifiers>
<PupilID>1234eqwe5</PupilID>
<Surname>BOBBY</Surname>
<Forename>HOPE</Forename>
<Gender>M</Gender>
<DOB>1997-02-18</DOB>
</PupilIdentifiers>
<Attendance>
<TermlyAttendance>
<SessionsPossible>89</SessionsPossible>
<SessionDetails>
<SessionDetail>
<AttendanceReason>S</AttendanceReason>
<AbsenceSessions>60</AbsenceSessions>
</SessionDetail>
<SessionDetail>
<AttendanceReason>X</AttendanceReason>
<AbsenceSessions>30</AbsenceSessions>
</SessionDetail>
</SessionDetails>
</TermlyAttendance>
</Attendance>
</Pupil>
</Pupils>'
SELECT
Pupil.value('PupilID[1]' ,'varchar(15)') as UPN,
Pupil.value('Surname[1]' ,'varchar(10)') as Surname,
Pupil.value('Forename[1]' ,'varchar(10)') as Forename,
att.value('SessionsPossible[1]' ,'int') as SessionsPossible,
det.value('AttendanceReason[1]' ,'varchar(5)') as AttendanceReason,
det.value('AbsenceSessions[1]' ,'varchar(5)') as AttendanceSessions
FROM
@x.nodes('//Pupils/Pupil/PupilIdentifiers') Pupils(Pupil)
OUTER APPLY Pupil.nodes('../Attendance/TermlyAttendance') Term(att)
OUTER APPLY att.nodes('SessionDetails/SessionDetail') Sess(det)
/*
UPN Surname Forename SessionsPossible AttendanceReason AttendanceSessions
--------------- ---------- ---------- ---------------- ---------------- ------------------
12345 BOB HOPE 96 I 6
12345 BOB HOPE 96 M 1
87389373 Shaun Allcock 109 NULL NULL
1234eqwe5 BOBBY HOPE 89 S 60
1234eqwe5 BOBBY HOPE 89 X 30
*/
XQuery Labs - A Collection of XQuery Sample Scripts