pagspi pagspi - 2 months ago 8
SQL Question

Retrieve all XML elements with the same prefix in SQL Server

I have an XML file in a format similar to:

<XML>
<Field1>100</Field1>
<Field2>200</Field2>
<Field3>300</Field3>
<Test>400</Test>
</XML>


I need to write a query that will get all of the element values that start with
Field
. So given the XML above the result should be

FieldVal
--------
100
200
300
400


I've tried the following but it does not work:

Select
xc.value('text()', 'int')
From
@XMLData.nodes('/XML/[starts-with(name(), ''Field'')]') As xt(xc)


NOTE: I am well aware that this task could be easily done if I reformatted my XML but unfortunately I have no control over the format of the XML.

Answer

One way is

declare @XMLData xml ='<XML>
   <Field1>100</Field1>
   <Field2>200</Field2>
   <Field3>300</Field3>
   <Test>400</Test>
</XML>'

Select 
    xc.value('.', 'int')
From @XMLData.nodes('/XML/*') As xt(xc)
WHERE xc.value('local-name(.)', 'varchar(50)') LIKE 'Field%'
Comments