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.