SQL Server 05: Reading values from XML datatype in a query

Question

I'm trying to get all of the attributes and node values from XML in a SQL query.

I tried doing the following, but I'm missing out few values in the output. Here is what I tried:

DECLARE @Details xml
SET @Details = '<root>
    <SelectedCategory CategoryId="101">
        <ItemID>120</ItemID>
        <ItemID>256</ItemID>
    </SelectedCategory>
    <SelectedCategory CategoryId="125">
        <ItemID>158</ItemID>
        <ItemID>120</ItemID>
    </SelectedCategory>
    <SelectedCategory CategoryId="15">
        <ItemID>5986</ItemID>
        <ItemID>20</ItemID>
     <ItemID>268</ItemID>
    </SelectedCategory>
</root>'
SELECT 
   SelCat.CatDet.value('(@CategoryId)[1]', 'int') as "CategoryID",
   SelCat.CatDet.value('.', 'int') as "ItemID"
FROM @Details.nodes('/root/SelectedCategory') as SelCat(CatDet)

This is showing the output as:

CategoryID     ItemID
101            120256
125         158120
15           598620268

While the desired output could be in any of the following:

CategoryID     ItemID
    101        120
    101        256
    125     158
    125        120
    15       5986
    15         20
    15         268

OR

CategoryID     ItemID
    101        120
    NULL       256
    125     158
    NULL       120
    15       5986
    NULL       20
    NULL       268

Anybody know how to achieve this?

Answer 1

Try something like this

DECLARE @Details xml
    SET @Details = '<root>
        <SelectedCategory CategoryId="101">
            <ItemID>120</ItemID>
            <ItemID>256</ItemID>
        </SelectedCategory>
        <SelectedCategory CategoryId="125">
            <ItemID>158</ItemID>
            <ItemID>120</ItemID>
        </SelectedCategory>
        <SelectedCategory CategoryId="15">
            <ItemID>5986</ItemID>
            <ItemID>20</ItemID>
         <ItemID>268</ItemID>
        </SelectedCategory>
    </root>'

    SELECT  T.[CategoryID],
      T2.Loc.query('.').value('.', 'INT') Val
    FROM
     ( 
      SELECT 
         SelCat.CatDet.value('(@CategoryId)[1]', 'int') as CategoryID,
         SelCat.CatDet.query('.') as ItemID
      FROM @Details.nodes('/root/SelectedCategory') as SelCat(CatDet)
      ) T
      CROSS APPLY ItemID.nodes('/SelectedCategory/ItemID') as T2(Loc)