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

Question

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.

Answer 1

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.