Reading XML nodes with namespaces in SQL Server

Reading XML nodes with namespaces in SQL Server

Like me, many of you might have gone through situations where you might had to parse and read XML in SQL Server. SQL Server 2005 onward have a very good functional support to read XML. Lets go over some examples on how to do so 

1. The first XML is an xml with persons details and contact details and here is how we can read that below

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Person>
<firstName>Kaushik</firstName>
<lastName>Ghosh</lastName>
<skill>ASP.NET</skill>
<language montherTongue="Bengali">Hindi</language>
<age>31</age>
</Person>
<Contacts>
<primaryEmail>primary@mydomain.com</primaryEmail>
<secondaryEmail>secondary@mydomain.com</secondaryEmail>
<primaryPhone>1234567890</primaryPhone>
<secondaryPhone>Melon</secondaryPhone>
</Contacts>
</SampleXML>'

SELECT
a.b.value('Person[1]/firstName[1]','varchar(10)') AS firstName,
a.b.value('Person[1]/lastName[1]','varchar(10)') AS lastName,
a.b.value('Person[1]/skill[1]','varchar(10)') AS skill,
a.b.value('Person[1]/language[1]/@montherTongue','varchar(10)')+ ',' + a.b.value('Person[1]/language[1]','varchar(10)') AS language,
a.b.value('Person[1]/age[1]','varchar(10)') AS age,
a.b.value('Contacts[1]/primaryEmail[1]','varchar(30)') AS primaryEmail,
a.b.value('Contacts[1]/secondaryEmail[1]','varchar(30)') AS secondaryEmail,
a.b.value('Contacts[1]/primaryPhone[1]', 'varchar(10)') AS primaryPhone,
a.b.value('Contacts[1]/secondaryPhone[1]', 'varchar(10)') AS secondaryPhone
FROM @MyXML.nodes('SampleXML') a(b)

In the above example it works fine, when the nodes are not repeating, but what if the XML has repeating nodes, please see the next example. This time we will take an example which will have namespaces, nested nodes with attributes and also multiple repeating nodes.

DECLARE @xml_text xml;
SET @xml_text = '
<towers xmlns="http://www.mydomain.org/schemas/xmls" version="1.4.1.0">
<tower xmlns="http://www.mydomain.org/schemas/xmls" uid="2183256">
<name>J.D. TAYLOR 24 #1</name>
<numAPI>0100320139</numAPI>
<towerLocation uid="Location1">
<latitude uom="dega">20.329705810</latitude>
<longitude uom="dega">-27.720651970</longitude>
</towerLocation>
<towerDatum uid="GL">
<code>GL</code>
<name>Ground Level</name>
</towerDatum>
<operator>Mobile comp1</operator>
<country>Unknown</country>
<county>DuPage</county>
<state>Illinois</state>
<timeZone>-00:00</timeZone>
<commonData>
<dTimCreation>2013-10-04T18:51:09.377</dTimCreation>
<dTimLastChange>2013-10-04T18:51:09.377</dTimLastChange>
</commonData>
</tower>
<tower xmlns="http://www.mydomain.org/schemas/xmls" uid="2183256">
<name>K.D. TAYLOR 24 #1</name>
<numAPI>0100320140</numAPI>
<towerLocation uid="Location2">
<latitude uom="dega">10.329705811</latitude>
<longitude uom="dega">-17.720651970</longitude>
</towerLocation>
<towerDatum uid="SL">
<code>SL</code>
<name>Sea Level</name>
</towerDatum>
<operator>Mobile Company 2</operator>
<country>Unknown</country>
<county>Cook</county>
<state>ILLINOIS</state>
<timeZone>-00:00</timeZone>
<commonData>
<dTimCreation>2013-11-04T18:51:09.377</dTimCreation>
<dTimLastChange>2013-11-04T18:51:09.377</dTimLastChange>
</commonData>
</tower>
</towers>';

with xmlnamespaces('http://www.mydomain.org/schemas/xmls' as towers,
default 'http://www.mydomain.org/schemas/xmls')
    SELECT  T.Item.value('(name/text())[1]', 'varchar(200)') as name,
T.Item.value('(numAPI/text())[1]', 'varchar(30)') as numAPI,
T.Item.value('towerLocation[1]/@uid[1]', 'varchar(20)') as towerLocation,
T.Item.value('towerLocation[1]/latitude[1]', 'varchar(20)') + ' ' 
+ T.Item.value('towerLocation[1]/latitude[1]/@uom[1]', 'varchar(10)') as latitude,
T.Item.value('towerLocation[1]/longitude[1]', 'varchar(20)') + ' ' 
+ T.Item.value('towerLocation[1]/longitude[1]/@uom[1]', 'varchar(10)') as longitude,
T.Item.value('towerDatum[1]/@uid[1]', 'varchar(10)') as towerDatumCode,
T.Item.value('towerDatum[1]/code[1]', 'varchar(10)') + ' or ' 
+ T.Item.value('towerDatum[1]/name[1]', 'varchar(20)') as towerDatum,
T.Item.value('(operator/text())[1]', 'varchar(200)') as operator,
T.Item.value('(country/text())[1]', 'varchar(30)') as country,
T.Item.value('(county/text())[1]', 'varchar(30)') as county,
T.Item.value('(state/text())[1]', 'varchar(30)') as state,
T.Item.value('(timeZone/text())[1]', 'varchar(30)') as timeZone,
T.Item.value('commonData[1]/dTimCreation[1]', 'varchar(30)') as created,
T.Item.value('commonData[1]/dTimLastChange[1]', 'varchar(30)') as changed
    FROM    @xml_text.nodes ( 'towers/tower' ) AS T(item)

The above XML is a more realistic example of XML data that you might need to parse in the database while doing some sort of integration.

Passing array of parameters to UDF/Stored Proc in SQL Server

With the beautiful XML support from SQL Server 2005 onwards, passing multiple values to Stored Procedure or User Defined Function has never been so easy. Please see the sample code

-- Pass all employeeId or other information together
-- in a sincle call of the stored procedure

DECLARE @x xml, @EMPID INT
SELECT @x =
  N'<Root>
      <Employee empId="1"/>
    </Root>'
SELECT E.EMPLOYEEID FROM TBLEMP E   
JOIN @x.nodes('Root/Employee') AS T(Item)
ON E.EMPLOYEEID = T.Item.value('@empId', 'int')

-- Writting the stored procedure

CREATE PROCEDURE SP_EMPLOYEES @ids xml AS
   SELECT E.EMPLOYEEID, E.MANAGERID, E.NAME
   FROM   TBLEMP E
   JOIN   @ids.nodes('/Root/Employee') AS T(Item)
     ON   E.EMPLOYEEID = T.Item.value('@num', 'int')
go

-- Test the procedure with the following
EXEC SP_EMPLOYEES N'<Root><Employee num="1"/><Employee num="2"/>
                             <Employee num="3"/><Employee num="4"/></Root>'