Friday, February 11, 2011

How do I choose parent node attribute and its child values using XML and Xquery?

I have this:

<pss>
<ps n="А parent node" m="654564654" t="435,8551" a="2857,2716">
      <sc s="a1" a="25,4220"/>
      <sc s="a2" a="0"/>
      <sc s="a3" a="2395,9945"/>
</ps>
...
</pss>

I need to select "А parent node m attribute and a1-a2 a values in one query. i tried this but it doesnt work:

SELECT ps.value('@m', 'nvarchar(50)') "parent node",
--       sc.value('@a1', 'nvarchar(50)') "название услуги",
--       sc.value('@a2', 'nvarchar(50)') "стоимость услуги",
       ps.value('@a3, 'nvarchar(50)') "b","
FROM   mts.dbo.bill 
OUTER APPLY xCol.nodes('//Report/rp/pss/ps') AS A(ps)
OUTER APPLY xCol.nodes('//Report/rp/pss/ps/sc/.') AS B(sc)

thanx in advance.

  • There are no a1, a2 or a3 attributes in the provided XML document!

    Use:

      /pss/ps/@*[name(.)='n' or name(.)='m']
    |
      /pss/ps/sc/@a
    

    This is the XPath expression that selects the wanted nodes from the provided XML document. I am not sure how it should be combined into an SQL command.

    Kai Osmon : thanx, but ERROR: XQuery [mts.dbo.bill.xCol.nodes()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:name()'.
    Kai Osmon : "There are no a1, a2 or a3 attributes in the provided XML document!" - this is attribute "s" of node, sorry
    Dimitre Novatchev : @Kai: My answer is in pure XPath 1.0 -- you didn't specify XQuery as a tag!. Now I updated the answer and it will run both in XPath 1.0 and XPath 2.0 (XQuery is a superset of XPath 2.0).
    Kai Osmon : Dimitre. I am quite new to all thу X... stuff. Will you please give me a solution to thу xml at the very top: is the root node and it has several nodes. nodes have nodes. What I need is: Get an attribute of node and all atributes of its nodes (repeats for each node).
    Kai Osmon : I need a query to have the above selected...

0 comments:

Post a Comment