Databases
StackOverflow2013
Postsdbo
POOPENXML with xmlns:dt
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOPENXML with xmlns:dt
    text
    copied!<p>Use OPENXML to get dt element in MSSQL 2005. How can I get xmlns:dt element in xml? For example, get a result set of two rows that list product id and country code. </p> <p>121403 GBR</p> <p>121403 USA</p> <pre><code>declare @xmldata xml set @xmldata = '&lt;?xml version="1.0"?&gt; &lt;data xmlns="http://www.aaa.com/master_browse_response" xmlns:dt="http://www.aaa.com/DataTypes"&gt; &lt;products&gt; &lt;product&gt; &lt;product_id&gt;&lt;![CDATA[121403]]&gt;&lt;/product_id&gt; &lt;countries&gt; &lt;dt:country&gt;GBR&lt;/dt:country&gt; &lt;dt:country&gt;USA&lt;/dt:country&gt; &lt;/countries&gt; &lt;/product&gt; &lt;/products&gt; &lt;/data&gt;' DECLARE @hDoc int, @rootxmlns varchar(100) SET @rootxmlns = '&lt;root xmlns:hm="http://www.aaa.com/master_browse_response"/&gt;' EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns SELECT * FROM OPENXML(@hDoc, '//hm:product',2) WITH ([hm:product_id] int , [hm:countries] varchar(100)) --clean up EXEC sp_xml_removedocument @hDoc </code></pre> <p>Here is one solution that I know by using xmlEdgeTable, but I am looking for a better solution.</p> <pre><code>DECLARE @hDoc int, @rootxmlns varchar(100) SET @rootxmlns = '&lt;root xmlns:hm="http://www.aaa.com/master_browse_response"/&gt;' EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns CREATE TABLE #xmlEdgeTable ( id int, parentid int, localname varchar(20), [text] varchar(20) ) INSERT INTO #xmlEdgeTable SELECT id, parentid,localname, cast([text] as varchar(20)) FROM OPENXML(@hDoc, '//hm:product',2) SELECT t6.text, t2.text FROM #xmlEdgeTable AS t1 INNER JOIN #xmlEdgeTable AS t2 ON t1.id = t2.parentid INNER JOIN #xmlEdgeTable AS t3 ON t3.id = t1.parentid INNER JOIN #xmlEdgeTable AS t4 ON t4.id = t3.parentid INNER JOIN #xmlEdgeTable AS t5 ON t4.id = t5.parentid INNER JOIN #xmlEdgeTable AS t6 ON t5.id = t6.parentid WHERE t1.localname = 'country' and t5.localname ='product_id' --clean up EXEC sp_xml_removedocument @hDoc DROP TABLE #xmlEdgeTable </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload