Get all XML elements with the same prefix in SQL Server

Question

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 element values ​​starting with Field. Therefore, given XML, the result should be

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

I 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 can be easily accomplished if I reformat my XML, but unfortunately I do not control the XML format.

Answer 1

One of the methods -

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%'

Answer 2

The name of the prefix with a special character and instead contains a check.

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

select t.n.value('.','varchar(100)')
from @x.nodes ('XML/*[contains(concat("$",local-name()),"$Field")]') t(n);

Answer 3

, , :

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

SELECT Fld.value('.','int') AS FieldOnly
FROM @xml.nodes('/XML/*[substring(local-name(.),1,5)="Field"]') AS A(Fld)

- :

DECLARE @fldName VARCHAR(100)='Field';
SELECT Fld.value('.','int') AS FieldOnly
FROM @xml.nodes('/XML/*[substring(local-name(.),1,string-length(sql:variable("@fldName")))=sql:variable("@fldName")]') AS A(Fld)

Change the first line to "Test" ( case sensitive! ) And you will only get one line with 400 ...