I need to extract the attribute values and insert those into another db table with columns like: key_old, key_new, transform1, value_old, value_new, description.
I'm struggling with writing a SELECT query that will get me the correct result set. Can someone please, guide me through this?
So far I came up with:
SELECT XMLType(xmldoc).extract('/transform//key/value/@description') -- all 'description' attribute values of <value> element
from xml_documents tbl
DataDirect XQuery is bundled in Stylus Studio, and you can experiment with it. The solution to your problem would look something similar to this, assuming "demo" is the table containing the result:
for $row in collection("pubs.dbo.xml_documents")/xml_documents
return
let $xmldoc := (# ddtek:evaluate-in-memory #) {$row/xmldoc}
return
for $key in $xmldoc/TRANSFORM/KEY
for $value in $key/VALUE
return
ddtek:sql-insert("demo", "key_old", string($key/@OLD), "key_new", string($key/@new), "transform1", string($key/@TRANSFORM1), "value_old", string($value/@OLD), "value_new", string($value/@new), "description", string($value/@DESCRIPTION))