We have reviewed how to read XML with SQL server in this post, and how to read XML having 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.
<SQLYoganame="Tejas Shah"companyname="SQLYoga"expertise="SSIS, SSRS, T-SQL"></SQLYoga>
Query to achieve the same:
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.2: SELECT @XML ='<SQLYoga3: name="Tejas Shah"4: companyname="SQLYoga"5: expertise="SSIS, SSRS, T-SQL"></SQLYoga>'6: SELECT8: x.v.value('.','VARCHAR(100)') AttributeValue9: FROM @XML.nodes('//@*') x(v)
Reference: Tejas Shah (www.SQLYoga.com)