I have the following xml:
<root>
<123 type="line">
<aa type="item">
<value>1</value>
</aa>
</123>
<234 type="line">
<bb type="item">
<value>2</value>
</bb>
</234>
</root>
How can I from bulk loading the xml into a database table (field xmldata)
query the xml so I get all values under type="line"
and then all values under type="item"
so I can then get to the value attribute. I can't hard code the attribute (eg 123) because it changes all the time, but the type value will remain constant.
Best Answer
You should use the nodes() method of the XML data type to shred your XML.
If I understand you correctly you want to fetch the nodes from the root node where the attribute
type
isline
and from that node you want the nodes where the attributetype
is 'item'. I will just assume that the XML you have provided with numeric node names is not the actual XML you are using but that the node names are valid XML names.A breakdown of the xPath in the nodes function:
From
/root
give me all nodes/root/*
where the attribute type is line/root/*[@type = "line"]
and from there give me all nodes/root/*[@type = "line"]*
where the attribute type is item/root/*[@type = "line"]/*[@type = "item"]
.Finally get the
value
node/root/*[@type = "line"]/*[@type = "item"]/value
and extract the text node using the value() function.