Blog Detail

SQL Yoga: Parse XML with namespace with SQL SERVER
16 Jul 2013
Tejas Shah
/Project/ImageURL/SQLYoa Parse XML_thumb[14].png
Recently, we were working on XML having namespace and we need to read that XML to fetch the information from the XML. 
 

Here, is the Sample XML which we are trying to parse:

DECLARE @x XML
SELECT @x = '
    <X 
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">
        <info>Sample XML TEST</info>        
    </X>            
  '

If we need to fetch the “info” element with T-SQL we can write query as follows:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/search/local/ws/rest/v1')
    SELECT  x.v.value('info[1]','VARCHAR(50)')
    FROM    @x.nodes('/X') x (v)

Here, we have to use “WITH XMLNAMESPACES”, so we can specify the namespace of the XML that we are reading, else it wont give us any result.
 

Tags

SQLYoga T-SQL tip Read XML T-SQL Parse XML XML XMLNAMESPACES

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post