4

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

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.

pagspi
  • 53
  • 5

3 Answers3

4

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%'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • This was exactly what I needed. I was unaware that you could use the value in the where clause. I had yet to see an example where that was done. Thank you very much! – pagspi Sep 28 '16 at 20:08
  • @pagspi In such a small XML this is absolutely OK, but in bigger structures (or a lot of them) I would prefer an approach where you do not have to read **all data** just to filter it **afterwards**. Your attempt to place the filter *within XQuery* is better in my eyes... – Shnugo Sep 28 '16 at 20:10
  • 1
    @Shnugo: XML processing in SQL has terrible performance in general (compared to relational processing). I wouldn't even feel comfortable betting on whether filtering inside or outside the XML is better in this case. I'd say all solutions are viable until measuring shows otherwise. (That and the XQuery in SQL is very limited in functions, so some filtering isn't even possible.) – Jeroen Mostert Sep 28 '16 at 20:48
  • @JeroenMostert, one more thougth about *performance*... With an XML with hundreds of nodes and this coming from a table with many rows I'd bet that a `.nodes('/root/*')` and a late `WHERE`-filter on `local-name(.)` would be worse. Simply because the *terrible performaning* XML engine would have to read many unneeded nodes, just to throw this away at the end. With a small example like this it does not make any noticeable difference... But *early reduction* is a quite general principle... – Shnugo Sep 29 '16 at 10:34
  • @Shnugo: The XML engine reads all of those nodes anyway in order to filter them, there's no way it can optimize the filter function. The question then is whether coughing up the unnecessary values to the relational engine is more overhead than applying the filter function, and on that I have no numbers. I'd *expect* early filtering to be faster as well, but like I said, I wouldn't take bets on it. You're quite right that in general, you want filtering to be as close to the source as possible. – Jeroen Mostert Sep 29 '16 at 10:39
4

Prefix name with special character and check contains instead. 

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);
Serg
  • 22,285
  • 5
  • 21
  • 48
4

I think it's this what you are looking for:

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)

Just because of the discussion in comments:

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'd get just the one row with 400...

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Seems quite a neat way. +1 – Martin Smith Sep 28 '16 at 20:10
  • Replacing `5` with `string-length("Field")` makes for slightly more obvious code. Even so it painfully demonstrates the limits of the XQuery dialect supported. – Jeroen Mostert Sep 28 '16 at 20:11
  • Thanks @Shnugo this solution works as well and it handles it all in the xquery so that I don't have to load the entire XML and the filter. – pagspi Sep 28 '16 at 20:27
  • @JeroenMostert, well, if this "Field" is hardcoded, I would not see an advantage. You'd find two times the literal.... If you'd need this dynamically, one might use a variable and use `let $var:= ...`, `string-length()` and `sql:variable("@varName")` to get this fully generically :-) – Shnugo Sep 28 '16 at 20:30
  • 1
    The advantage is that you don't have to explain (or manually count) the `5`. Magic constants are always iffy. In this case, if you decided you wanted to match `Fields` instead, it'd be easier to forget to update the `5` than to forget updating both instances of `Field`. – Jeroen Mostert Sep 28 '16 at 20:32
  • @JeroenMostert, Sure, I know this... But if you would expect changing values here, you'd be better of with a variable anyway... – Shnugo Sep 28 '16 at 20:35
  • Yeah, except nobody ever expects to change a value until they do. :-) It's a minor point and a personal preference. I've long since stopped counting string lengths manually and leave this up to the compiler, regardless of what language I'm working in. I'd use `"Field".Length` in C# too, even though this is way more keystrokes than `5`. – Jeroen Mostert Sep 28 '16 at 20:37