I hope someone can help me with this XMLTABLE question.
I have XML data stored in a column of a table. I want to map the contents of the XML into relational rows and columns. My request is rather simple: I would like each name of node in the XML to become a column, the contents of the column equal to the name of the node.
Here is what rows in my table look like:
ID XML_CONTENT
------ ----------------------------------------
832-Q <LevelOne>
<LevelTwo_A>
<LevelThree_1>
<h1>3.4</h2>
<h2>5.6</h2>
</LevelThree_1>
<LevelThree_2>
<h1>1.2</h2>
<h2>8.2</h2>
</LevelThree_2>
<LevelThree_3>
<h1>5.2</h2>
<h2>5.22</h2>
</LevelThree_3>
</LevelTwo_A>
<LevelTwo_A>
<LevelThree_4>
<h1>3.9</h2>
<h2>1.9</h2>
</LevelThree_4>
</LevelTwo_A>
</LevelOne>
562-B <LevelOne>
<LevelTwo_B>
<LevelThree_1>
<h1>32.4</h2>
<h2>5.5</h2>
</LevelThree_1>
<LevelThree_4>
<h1>10.2</h2>
<h2>8.1/h2>
</LevelThree_4>
</LevelTwo_B>
</LevelOne>
And what I would like as output is something like this:
ID L2 L3 H1 H2
----- -------- ------------ ------- --------
832-Q LevelTwo_A LevelThree_1 3.4 5.6
832-Q LevelTwo_A LevelThree_2 1.2 8.2
832-Q LevelTwo_A LevelThree_3 5.2 5.22
832-Q LevelTwo_A LevelThree_4 3.9 1.9
562-B LevelTwo_B LevelThree_1 32.4 5.5
562-B LevelTwo_B LevelThree_4 10.2 8.1
One way to put this is that it is really the H1 and H2 elements I'm interested in. Each node name in the path leading up to that I want to become columns in my output. Note: My H1 and H2 elements are always at exactly the depth as indicated in the example. I'm not interested in the very first node name ('LevelOne') of XML_CONTENT, simply because in my case it is always similar among the rows in my table.
I've tried numerous forms of XMLTABLE but just cannot seem to get this right. At the same time I think my request is rather simple so I'm a bit embarrassed that I cannot figure this one out. Anyone can help ?
Thanks.
Edited by: user491370 on Apr 11, 2010 9:51 PM