How to get node name and values from an 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 a resultset with 2 columns (FieldName, FieldValue) like -

TotCR   222.10
PerCF     0.40
...
PerCFWM   1.50

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

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

marc_s · Accepted Answer · 2012-06-12 15:13:20Z

50

You can use the local-name() function to the get name of the XML node - 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 should give you an output something like:

enter image description here

answered Jun 12, 2012 at 15:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related