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.
From stackoverflow
Kai Osmon
-
There are no
a1,a2ora3attributes in the provided XML document!Use:
/pss/ps/@*[name(.)='n' or name(.)='m'] | /pss/ps/sc/@aThis 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" ofnode, 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...From Dimitre Novatchev
0 comments:
Post a Comment