TSQL XQuery to obtain two child nodes

Question

I'm working with XML data and want to return the values of two child nodes. I can get this work with a single node but not return two seperate columns. How do I write the xPath and xQuery to return two columns?

DECLARE @x XML
Set @x = '
<MT_BoxTextCtrl>
  <DataSpec>ShipID_3_1_1</DataSpec>
  <Label>Mode</Label>
  <Size>230,30</Size>
  <Units />
  <UserLoValue />
</MT_BoxTextCtrl>
<MT_BoxTextCtrl>
  <DataSpec>ShipID_3_1_2</DataSpec>
  <Label>Sub Mode</Label>
  <Size>230,30</Size>
  <Units />
  <UserLoValue />
</MT_BoxTextCtrl>
<MT_AlarmCtrl>
  <AlarmRngIsReversed>False</AlarmRngIsReversed>
  <CustomQuery />
  <DataSpec>ShipID_9_1_1</DataSpec>
  <HiValue>1</HiValue>
  <Label />
</MT_AlarmCtrl>
<MT_AlarmCtrl>
  <AlarmRngIsReversed>False</AlarmRngIsReversed>
  <CustomQuery />
  <DataSpec>ShipID_9_1_5</DataSpec>
  <HiValue>1</HiValue>
  <Label>In 500M DP Zone</Label>
</MT_AlarmCtrl>'


Select T.c.value('.', 'varchar(30)') as 'DataSpec'
from @x.nodes('//DataSpec') T(c)

Select T.c.value('.', 'varchar(30)') as Label
from @x.nodes('//Label') T(c)

thanks

Answer 1

Try this:

SELECT
    NodeType = XC.value('local-name(.)', 'varchar(25)'),
    DataSpec = XC.value('(DataSpec)[1]', 'varchar(30)'),
    Label = XC.value('(Label)[1]', 'varchar(30)')
FROM 
    @x.nodes('/*') XT(XC)

This basically takes every top-level node and returns a "virtual" table of those XML fragments. From those XML fragments, I grab the node name (the "type" of the XML node in question), the DataSpec and the Label subelements (their textual values).

I get an output like this: