-2
<abc id="abc1">
  <def id="def1">
    <ghi att='ghi1'>
      <mn id="0742d2ea" name="RF"  dt="0" df="3" ty="0" />
      <mn id="64d9a11b" name="CJ"  dt="0" df="3" ty="0" />
      <mn id="db72d154" name="FJ"  dt="2" df="4" ty="0" />
      <mn id="39af9fa1" name="BS"  dt="0" df="2" ty="0" />
    </ghi>
    <jkl  att='jkl1'>
       <mn id="0742d2ea" name="RF" dt="1" gl="19" />
       <mn id="64d9a11b" name="CJ" dt="0" gl="6" />
       <mn id="db72d154" name="FJ" dt="0" gl="0" />
       <mn id="39af9fa1" name="BS" dt="0" gl="12" />
       <mn id="ac4f566f" name="DJ" dt="0" gl="9" />
       <mn id="4bf3ba2f" name="RP" dt="0" gl="16" />
       <mn id="db1af021" name="SC" dt="1" gl="10" />
       <mn id="c4c93a2d" name="DN" dt="1" gl="15" />
    </jkl>
  </def>
</abc>

I need this output. Is this possible in SQL Server 2008?

id          name ghiDT  ghiDF   ghiTY   jklDT   jklGL
0742d2ea    RF  0   3   0   1   19
64d9a11b    CJ  0   3   0   0   6
db72d154    FJ  2   4   0   0   0
39af9fa1    BS  0   2   0   0   12
ac4f566f    DJ  0   0   0   0   9
4bf3ba2f    RP  0   0   0   0   16
db1af021    SC  0   0   0   1   10
c4c93a2d    DN  0   0   0   1   15
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
user1332896
  • 13
  • 1
  • 1
    **YES!** it's definitely possible - read [Introduction to XQuery in SQL Server](http://msdn.microsoft.com/en-us/library/ms345122%28v=sql.90%29.aspx), try something yourself, and if you're stuck, come back and ask more precisely – marc_s Apr 14 '12 at 07:14

1 Answers1

0

Don't really understand where the cross tab is applicable. You have to query the nodes in each group separately using nodes() to shred the XML. Then you can do a full outer join on id and use coalesce to make sure you get the first non null value forid and name columns. You should also use coalesce to get a 0 instead of null for your other columns.

SE-Data

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281