Fetch Data From XML With Namespace in SQL Server

Introduction

These days XML data is used extensively in applications. SQL Server also manage XML data efficiently to fetch data from XML content. Here we will discuss how to fetch data when XML contains Namespace tag. Let’s start:

 

Using Code

The following code snippet defines a XML variable with namespace tag (xmlns) containing “http://tempnamespace/default”, 

DECLARE @data XML;
SET @data = '<EmplyoeeInfo xmlns="http://tempnamespace/default">
                <Employee ID="1">
                    <Name>Manas</Name>
                    <Profession>Software</Profession>
                </Employee>
             </EmplyoeeInfo>';

Now have a look at the following code snippet to fetch data,

WITH XMLNAMESPACES (DEFAULT 'http://tempnamespace/default')
SELECT  @data.value('(EmplyoeeInfo/Employee/@ID)[1]', 'nvarchar(100)') as ID,
        @data.value('(EmplyoeeInfo/Employee/Name)[1]', 'nvarchar(100)') as Name,
        @data.value('(EmplyoeeInfo/Employee/Profession)[1]', 'nvarchar(100)') as Profession;

In proceeding code it uses with XML Namespaces which provides namespace URI support in the following ways:

• It makes the namespace prefix to URI mapping available when Constructing XML Using FOR XML queries.
  
  
• It makes the namespace to URI mapping available to the static namespace context of the xml Data Type Methods. 

We use the WITH XMLNAMESPACES clause in a statement that also includes a common table expression(CTE). See the output mentioned in Figure1.

 

Note:
 
If we don’t use WITH XMLNAMESPACES in query it will return a record with NULL values. If XML do not contain any namespace then it doesn’t require to add WITH XMLNAMESPACES.

 

Output

 

 

    Figure1: Output of XML Data 

 

Conclusion

In this blog we discussed how to read data when XML contains Namespace tag. Using WITH XMLNAMESPACES we can generate XML data with namespace tag through “For XML RAW”.

Reference

• Add Namespaces to Queries with WITH XMLNAMESPACES