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.