SQL Server shred XML to temp table

问题:

I have the following XML:

<query>
  <param weight="0.3">
    <item type="1" low="18" hi="20" pos="1" />
    <item type="1" low="220" hi="220" pos="0" />
  </param>
  <param weight="0.7">
    <item type="2" low="5" hi="5" pos="1" />
  </param>
</query>

I want it to result in the following data set:

1   0.3 1   18  20  1
2   0.3 1   220 220 0
3   0.7 2   5   5   1

ROW_NUMBER() would make the row number. I'm only getting the first row (item[1]), but not the others. If I remove the '[1]', I get a "singleton error". How do I write this?

This is what I have so far:

SELECT  x.node.value('@weight', 'float') As [Weight],
        x.node.value('(item/@type)[1]', 'int') AS [Type],
        x.node.value('(item/@low)[1]', 'float') AS Low,
        x.node.value('(item/@hi)[1]', 'float') AS Hi,
        x.node.value('(item/@pos)[1]', 'bit') AS Pos
FROM    @Input.nodes('/query//*') AS x(node)

回答1:

I figured it out with OPENXML:

INSERT  INTO @ref
SELECT  *
FROM    OPENXML (@idoc, '/query/param/item', 2)
WITH    ([Weight]   float   '../@weight',
        ParamType   int     '@type',
        Low         float   '@low',
        Hi          float   '@hi',
        Pos         bit     '@pos')

回答2:

The following query should work.

SELECT  x.node.value('../@weight', 'float') As [Weight], 
        x.node.value('(@type)[1]', 'int') AS [Type], 
        x.node.value('(@low)[1]', 'float') AS Low, 
        x.node.value('(@hi)[1]', 'float') AS Hi, 
        x.node.value('(@pos)[1]', 'bit') AS Pos 
FROM    @Input.nodes('/query/param/item') AS x(node) 

回答3:

Your way is definitely easier than mine, but it challenged me to play with FLWOR:

DECLARE @XML XML= '<query> 
  <param weight="0.3"> 
    <item type="1" low="18" hi="20" pos="1" /> 
    <item type="1" low="220" hi="220" pos="0" /> 
  </param> 
  <param weight="0.7"> 
    <item type="2" low="5" hi="5" pos="1" /> 
  </param> 
</query> 
'


SELECT  x.node.query('.').value('(//weight)[1]', 'float') AS [Weight]
      , x.node.query('.').value('(//type)[1]', 'int') AS [Type]
      , x.node.query('.').value('(//low)[1]', 'float') AS Low
      , x.node.query('.').value('(//hi)[1]', 'float') AS Hi
      , x.node.query('.').value('(//pos)[1]', 'bit') AS Pos
FROM    ( SELECT    @XML.query('for $param in /query/param
                    return 
                        for $item in $param/item
                        return 
                            <item>
                                <weight> {data($param/@weight)} </weight>
                                <type> {data($item/@type) } </type>
                                <low> {data($item/@low) } </low>
                                <hi>  {data($item/@hi) } </hi>
                                <pos> {data($item/@pos) } </pos>
                            </item>
                        ') AS result
        ) q
        CROSS APPLY result.nodes('./item') AS x ( node )