Modify XML document inside a stored procedure

Posted on 2008-11-13
Last Modified: 2012-05-05
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.
Question by:troycomp
    LVL 59

    Accepted Solution

    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.

    Author Comment

    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>
    SELECT @myDoc

    -- 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"
    SELECT @myDoc
    -- 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
    LVL 59

    Assisted Solution

    by:Kevin Cross
    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)

    Author Comment

    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.
    LVL 59

    Assisted Solution

    by:Kevin Cross
    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.
    LVL 20

    Expert Comment

    by:Marten Rune
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now