Help writing a query to extract XML

Question

Post reply

Help writing a query to extract XML

support 86837

SSC Enthusiast

Points: 143
More actions
November 25, 2014 at 6:09 pm

#397691

I've never done any XML data extraction before and have spent awhile this afternoon reading about it, and am still struggling. The concept seems relatively simple but my query still doesn't work. Any suggestions would be greatly appreciated!
Table name is CCHistory
Column containing the data is CCXML
and the xml format, names changed to protect the guilty, is:
<?xml version="1.0"?>
<CC>
<Type>1</Type>
<Exp>1112</Exp>
<Number>123456</Number>
<Name><![CDATA[RobRobertson]]></Name>
<Amt>18.06</Amt>
<Auth>33875</Auth>
</CC>
I'm simply trying to extract all the data from the XML along with the OrderID column from the table via a query, no WHERE clause as of yet. My latest try is as follows:
DECLARE @CCXML varchar(90);
SELECT CCH.OrderID, (@CCXML)
FROM tblCCHistory AS CCH
Thanks!
Dan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Aditya-313343 Right there with Babe Points: 768 More actions · Answer 1

Create a Temp table with exact structure of the xml

Open the xml document using sp_xml_preparedocument and then insert the data from xml into the temp table created above.

Once inserted removed the xml document using sp_xml_removedocument

exec sp_xml_preparedocument @hDoc OUTPUT, @xmlntextparameter

INSERT INTO @temptable(columns)

SELECT * FROM OPENXML(@hDoc, 'CC',2)

WITH ( Type varchar(10),Exp varchar(10),.....)

exec sp_xml_removedocument @hDoc

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

i assumed that you have a table which is having xml column.

DECLARE @xml TABLE (id INT IDENTITY(1,1), xmldoc XML)

INSERT INTO @xml

SELECT '<?xml version="1.0"?>

<CC>

<Type>1</Type>

<Exp>1112</Exp>

<Number>123456</Number>

<Name><![CDATA[RobRobertson]]></Name>

<Amt>18.06</Amt>

<Auth>33875</Auth>

</CC>'

UNION ALL

SELECT '<?xml version="1.0"?>

<CC>

<Type>2</Type>

<Exp>1113</Exp>

<Number>123456</Number>

<Name><![CDATA[RobRobertson000]]></Name>

<Amt>182.06</Amt>

<Auth>3387225</Auth>

</CC>'

UNION ALL

SELECT '<?xml version="1.0"?>

<CC>

<Type>3</Type>

<Exp>1114</Exp>

<Number>123456</Number>

<Name><![CDATA[RobRobertson001]]></Name>

<Amt>1812132.06</Amt>

<Auth>33875</Auth>

</CC>'

SELECT a.id, a.xmldoc

,x.l.value('Type[1]','INT') AS [Type]

,x.l.value('Exp[1]','INT') AS [Exp]

,x.l.value('Name[1]','VARCHAR(30)') AS [Name]

,x.l.value('Amt[1]','DECIMAL(18,2)') AS [Amt]

,x.l.value('Auth[1]','INT') AS [Auth]

FROM @xml a

CROSS APPLY xmldoc.nodes('CC') x(l)

hope it helps.

support 86837 SSC Enthusiast Points: 143 More actions · Answer 3

So the only way is to create a temporary table? I don't think our software vendor allows us that much access to the system. We can pull queries but I don't think they've given us permission for INSERTs and DELETEs, etc. - with good reason I suspect! Temp or virtual tables I don't know but I'm going to give it a try!

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

support 86837 (11/25/2014)
I've never done any XML data extraction before and have spent awhile this afternoon reading about it, and am still struggling. The concept seems relatively simple but my query still doesn't work. Any suggestions would be greatly appreciated!
Table name is CCHistory
Column containing the data is CCXML
and the xml format, names changed to protect the guilty, is:

you can use this code directly on the table of CCHistory. Temp/Variable table is just to simulate CCHistory table here to give you can idea how you can fetch the data in actual environment.