Modify XML document inside a stored procedure

I have 3 tables im updating inside my sproc. My 3rd table has a foreign key relationship to the primary key in my second table. The primary key in the second table is an identity column so sql server creates it. I need to get that value and add it to my xml file in passing in. Im using an xml file cause there will be multiple records i will be adding in the 3rd table. I need to modify the xml doc to add in the primary key value read from table 2.
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
See this TechNet article:

Do a CTRL+F (find) and search for .modify and you will be taken to example of using code like this:

SET xmldata.modify('replace value of (//node()[text()=sql:variable("@old")])[1] with sql:variable("@new")')

You can use literal in XPath/XQuery but showed with sql variable to find the old value and you would just select the key you want to inject into the xml document into a sql variable and type the variable name including the @ where you see @new.
troycompAuthor Commented:
This does what i want it do (almost)

DECLARE @myDoc xml
SET @myDoc = '<Root>
<Location LocationID="10"
            MachineHours=".2" >Manufacturing steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>

-- update text in the first manufacturing step
SET @myDoc.modify('
  replace value of (/Root/Location/step[1]/text())[1]
  with     "new text describing the manu step"
-- update attribute value
SET @myDoc.modify('
  replace value of (/Root/Location/@LaborHours)[1]
  with     "100.0"
select @myDoc

my xml file will be similar to this one but how do i loop through all the nodes and replace the values with my desired new one? This example does it one at a time
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
That is how it works, so you can build a user defined function that takes in xml, and replacement string unless it is constant and your function can return xml so you can do a normal update statement on your table and then you can do something like this:

UPDATE tablename
SET xmldoc = dbo.udf_modifyXML(xmldoc)
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

troycompAuthor Commented:
What i mean by one at a time is this, look at this line

replace value of (/Root/Location/step[1]/text())[1]

notice step[1]? the 1 is hardcoded and my xml file will have multple "step" nodes. I will know how many they will have and i will have to update everyone of them before inserting these records into my table. a udf will work but im not sure how to set up my select to loop through the xml file.
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Remove the [1] -- don't think that is needed and then you can do a test for -

step[text() != 'new text describing the manu step']

This way, you can just keep looping until no nodes are found/affected by the modification.  Not too sure other than that.  Maybe someelse has a better idea, but hopefully that gets you started.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Delete: No Refund

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.