SQL with Manoj

Namespaces like in every language provides a way to avoid conflict with element names. Here we will see how can we add Namespace to an XML. In previous posts we learned how to query XMLs, but without Namespaces. Here we will see how can we query XML containing Namespaces.

>> Creating an XML with Namespace prefix:

USE [AdventureWorks]
GO

;WITH XMLNAMESPACES ('uri' as ns)
select TOP 2 [ContactID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [EmailAddress], [Phone] 
from AdventureWorks.Person.Contact 
FOR XML PATH('Contact'), ROOT('ns:Person.Contact'), ELEMENTS XSINIL

Output:- XML with the Namespace prefix

<ns:Person.Contact 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:ns="uri">
  <Contact>
    <ContactID>1</ContactID>
    <Title>Mr.</Title>
    <FirstName>Gustavo</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Achong</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
    <Phone>398-555-0132</Phone>
  </Contact>
  <Contact>
    <ContactID>2</ContactID>
    <Title>Ms.</Title>
    <FirstName>Catherine</FirstName>
    <MiddleName>R.</MiddleName>
    <LastName>Abel</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
    <Phone>747-555-0171</Phone>
  </Contact>
</ns:Person.Contact>

>> Querying the XML with Namespaces:

declare @xml xml

set @xml = '
<ns:Person.Contact 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:ns="uri">
  <Contact>
    <ContactID>1</ContactID>
    <Title>Mr.</Title>
    <FirstName>Gustavo</FirstName>
    <MiddleName xsi:nil="true" />
    <LastName>Achong</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>gustavo0@adventure-works.com</EmailAddress>
    <Phone>398-555-0132</Phone>
  </Contact>
  <Contact>
    <ContactID>2</ContactID>
    <Title>Ms.</Title>
    <FirstName>Catherine</FirstName>
    <MiddleName>R.</MiddleName>
    <LastName>Abel</LastName>
    <Suffix xsi:nil="true" />
    <EmailAddress>catherine0@adventure-works.com</EmailAddress>
    <Phone>747-555-0171</Phone>
  </Contact>
</ns:Person.Contact>'

-- Declaring namespaces in XQuery
SELECT 
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
from @xml.nodes('declare namespace ns="uri"; //ns:Person.Contact/Contact') T(C)

-- Using WITH XMLNAMESPACES
;WITH XMLNAMESPACES ('uri' as ns)
SELECT 
	T.C.value('ContactID[1]', 'INT') as [ContactID],
	T.C.value('Title[1]', 'VARCHAR(8)') as [Title],
	T.C.value('FirstName[1]', 'VARCHAR(50)') as [FirstName],
	T.C.value('MiddleName[1]', 'VARCHAR(50)') as [MiddleName],
	T.C.value('LastName[1]', 'VARCHAR(50)') as [LastName],
	T.C.value('Suffix[1]', 'VARCHAR(10)') as [Suffix],
	T.C.value('EmailAddress[1]', 'VARCHAR(50)') as [EmailAddress],
	T.C.value('Phone[1]', 'VARCHAR(50)') as [Phone]
from @xml.nodes('//ns:Person.Contact/Contact') as T(C)