How to get name and node values ​​from xml variable in t-sql

Question

I have the following xml -

<Surveys xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="ImmForm XML Schema NHS Direct.xsd"><Svy SurveyName="WeeklyFluSurveillance2012/13-NHSDirectWeek40w/e07/10/2012" OrgCode="NHS Direct"><TotCR>222.10</TotCR><PerCF>0.40</PerCF><PerCFunder1>0.20</PerCFunder1><PerCF1to4>0.30</PerCF1to4><PerCF5to14>0.50</PerCF5to14><PerCF15to44>0.40</PerCF15to44><PerCF45to64>0.20</PerCF45to64><PerCF65plus>3.60</PerCF65plus> <PerCFNE>4.22</PerCFNE> <PerCFNW>6.50</PerCFNW> <PerCFYH>0.80</PerCFYH> <PerCFEM>1.00</PerCFEM> <PerCFWM>1.50</PerCFWM></Svy></Surveys> 

I need to select the child node name and its value in the result set with two columns (FieldName field, FieldValue), for example -

 TotCR 222.10 PerCF 0.40 ... PerCFWM 1.50 

The nodes in xml will change and may not always be the same. Even the values ​​can be integer or text.

Can you guys suggest how to do this using OPENXML in SQL Server 2008 R2?

Answer 1

You can use the local-name() function to get the XML node name - try something like this:

 DECLARE @input XML = '...your xml here.....' SELECT NodeName = C.value('local-name(.)', 'varchar(50)'), NodeValue = C.value('(.)[1]', 'varchar(50)') FROM @input.nodes('/Surveys/Svy/*') AS T(C) 

This will give you the result: