Extracting XML information
-
Hi,
I have a table which contains XML and I have no clue how to extract the information I need.
What I really need to be able to do is identify the objectid and ObjectSystemCode so I can then use those object IDs elsewhere.
Any help appreciated!
XML that's contained in the database is
<NewDataSet>
<XmlLink>
<ObjectId>123456</ObjectId>
<ObjectName>Jo Bloggs</ObjectName>
<ObjectSystemCode>CCT</ObjectSystemCode>
</XmlLink>
<XmlLink>
<ObjectId>654321</ObjectId>
<ObjectName>Jo Bloggs Ltd</ObjectName>
<ObjectSystemCode>CLNT</ObjectSystemCode>
</XmlLink>
</NewDataSet>
Also the xml is stored as ntext in the table
-
See if this helps
DECLARE @t TABLE(xmlCol NTEXT)INSERT INTO @t(xmlCol)
VALUES('<NewDataSet>
<XmlLink>
<ObjectId>123456</ObjectId>
<ObjectName>Jo Bloggs</ObjectName>
<ObjectSystemCode>CCT</ObjectSystemCode>
</XmlLink>
<XmlLink>
<ObjectId>654321</ObjectId>
<ObjectName>Jo Bloggs Ltd</ObjectName>
<ObjectSystemCode>CLNT</ObjectSystemCode>
</XmlLink>
</NewDataSet>');
WITH CTE AS (
SELECT CAST(xmlCol AS XML) AS xmlCol
FROM @t)
SELECT x.r.value('ObjectId[1]','INT') AS ObjectId,
x.r.value('ObjectName[1]','VARCHAR(20)') AS ObjectName,
x.r.value('ObjectSystemCode[1]','VARCHAR(20)') AS ObjectSystemCode
FROM CTE
CROSS APPLY xmlCol.nodes('/NewDataSet/XmlLink') AS x(r)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I've no idea (yet!) how that works, but it works a treat, thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply