SQL Server Performance Forum – Threads Archive

Parsing XML to update a table.

I am writing a stored procedure which will update a table. Table Name is ProgramTitle. It has the following columns: ID varchar(20),
TitleTypeId varchar(20),
TitleValue varchar(20) My procedure has the following inputs:
@ID
@txtTitleValue (This is an XML string containing TitleTypeId and TitleValue) I am using XML to parse the values (ie., for TitleTypeId and TitleValue) I have so far written the query which will parse the XML. No the problem I am facing is how to update the values in the ProgramTitle table. The requirement of the procedure is that I should be able to update the TitleTypeId and TitleValue for a given ID. Both TitleTypeId and TitleValue are given in input as XML string. Any suggestions. Thanks, StarWarsBigBang

---

How do you hold the values while parsing the xml string?

---

Hi ya, Have you checked out OPENXML, a SQL2000 extension for using XML? It will convert the xml into a table and does the parsing for you…?
the update statement would then be a simple join, since you have the original id and the new data Cheers
Twan

---

there are some issues with OPENXML,
always carefully check the execution plans when join XML tables to other tables,
don’t worry about the high row count on the XML,
pay attention to the join op to the permanent table
if that is badd, consider insertint the XML data to a table variable, then using that with your final target, see my article on XML and execution plans

---

oops sorry yes should have mentioned that, I’d always insert the xml into a temp table variable before joining it… Cheers
Twan

---

]]>