Blog Detail

SQL Yoga: Parse XML with namespace with SQL SERVER

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.
SQLYoa Parse XML

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.

Recent Post