Home > XML > Adding Namespaces in XML | Querying XML with Namespaces

Adding Namespaces in XML | Querying XML with Namespaces


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)
About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 405 other followers

%d bloggers like this: