Blog Detail

SQL SERVER T-SQL to read all attributes of the XML
16 Sep 2013
Tejas Shah
/Project/ImageURL/SQLYoga Read All Attributes of the XMLwithT-SQL1_thumb[4].png
We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespacein 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:
 
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.

Tags

SQLYoga Tejas Shah T-SQL tip Read all Attributes of XML Read XML T-SQL

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