SQLYoga: Read XML T-SQL

September 4, 2013

SQL SERVER: T-SQL to read all attributes of the XML

We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespace in this POST. Today I would like to share how to read all attributes of the XML and have result in Table format.

Recently, my team assigned one requirement to import XML file. To read an XML file is achieved by following this POST but here, we have a requirement to read each Attribute name and Attribute Value to store that info in normalized table by comparing Attribute Name. Let me explain with example.

XML:

Expected Result:

SQLYoga Read All Attributes of the XML with T-SQL #1

Query to achieve the same:

DECLARE @XML AS XML

SELECT @XML = ''

SELECT CAST(x.v.query('local-name(.)') AS VARCHAR(100)) AS AttributeName
 ,x.v.value('.', 'VARCHAR(100)') AttributeValue
FROM @XML.nodes('//@*') x(v)
ORDER BY AttributeName

Here, We have used “@*”, which gives us way to read the attributes for the ROOT tag and “local-name” gives is the Attribute name. That’s it.

Reference: Tejas Shah ( www.SQLYoga.com )

July 8, 2013

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.