Parse xml and create and update records in stored procedure

Question

Post reply

Parse xml and create and update records in stored procedure

Paul O'Flaherty

Mr or Mrs. 500

Points: 506
More actions
April 15, 2011 at 5:01 am

#236700

I'm passing xml into a stored procedure and I'm updating the records based on a unique record id. This is working fine. What I'm having a problem with is within this xml file the is an array of photos associated with this record id that i need to create records for in another table.
simplified XML example
<Record>
<RecordID>1</RecordID>
<Quantity>3</Quantity> --I can update the quantity table for this recordid
<Photos>
--Could be any number of photos - need to create entry in table for each photo storing recordid recordedo
</Photos>
</Record>
Can somebody point me to an article/example explaining how to acheive this?
Thanks
Paul

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

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

SQL Server's XML handling is pretty good. Basically you'll want to CROSS Apply using the XML.Nodes() method.

Here's the BOL reference:

http://msdn.microsoft.com/en-us/library/ms188282.aspx

srikant maurya Hall of Fame Points: 3293 More actions · Answer 2

declare @xml xml

set @xml='<Record>

<Quantity>3</Quantity> --I can update the quantity table for this recordid

--Could be any number of photos - need to create entry in table for each photo storing recordid recordedo

</Photos>

</Record>'

SELECT x.col.value('RecordID[1]', 'INT') AS RecordID,

x.col.value('Quantity[1]', 'INT') AS Quantity

FROM @xml.nodes('//Record/Photos') x(col)

Now u can insert and update the data inside the procedure

Paul O'Flaherty Mr or Mrs. 500 Points: 506 More actions · Answer 3

Hi Srikant

SELECT x.col.value('RecordID[1]', 'INT') AS RecordID,

x.col.value('Quantity[1]', 'INT') AS Quantity

FROM @xml.nodes('//Record/Photos') x(col)

Will the above not just return me the recordid an quantity?

I would like to be able to insert the photos into a temporary table for processing so if i had xml with these records:

--this would have to create 3 records in the temporary table

recordid 1

recordid photo 1

recordid photo 2

recordid photo 3

--this would have to create 2 records in the temporary table

recordid 2

recordid photo 1

recordid photo 2

Thanks

Paul

HowardW One Orange Chip Points: 29109 More actions · Answer 4

If you post some sample data that actually represents what you're trying to do e.g. an XML document that looks like the one your referring to with multiple records and photos people are more likely to give you a working sample. Otherwise, read the article I pointed you to.

Paul O'Flaherty Mr or Mrs. 500 Points: 506 More actions · Answer 5

I'll have a look at that, quick glance this seems to be what i'm looking for.

Thanks Howard