views:

30

answers:

0

I am using SQL Server 2005 to Extract XML data. I would appreciate some help extracting the following XML snippet?

<ELEMENT_A>
    <CHILD_A _attribA="ABC">
       <CHILD_B Type="1">
          <CHILD_C>
             <CHILD_D Type="1" Date="2010-08-31">
                <CHILD_E _attribB="M0">
                   <CHILD_F>-0.32295</CHILD_F>
                </CHILD_E>
            </CHILD_D>
         </CHILD_C>
     </CHILD_B>
   </CHILD_A>
</ELEMENT_A>

How do I get the data in the following tabular format? ELEMENT_A | _attribA | CHILD_A | CHILD_B | CHILD_C | CHILD_D | CHILD_E | CHILD_F

Note: I'd like to use select by nodes e.g. the following gets me the value of the first Attribute:
select v.value('@_attribA[1]','nvarchar(4)') from @doc.nodes('/ELEMENT_A/CHILD_A') x(v)

Very much appreciated, Thanks.