XML DML Script to replace nodes

Question

I have a table and xml data as below for example:

                IF EXISTS(
                  SELECT table_name FROM information_schema.tables 
                  WHERE table_name = 'JobCandidates')
                DROP TABLE JobCandidates;
                -- Create JobCandidates table 
                CREATE TABLE JobCandidates(
                  CandidateId INT PRIMARY KEY,
                -- Create typed XML column
                  CandidateResume XML 
                    (DOCUMENT HumanResources.HRResumeSchemaCollection) NULL,
                -- Create untyped XML column
                  CandidateRating XML NULL);
                -- Insert data into the typed column
                INSERT INTO JobCandidates (CandidateId, CandidateResume)
                  (SELECT JobCandidateId, [Resume]
                  FROM HumanResources.JobCandidate
                  WHERE JobCandidateId = 1);
                UPDATE JobCandidates
                SET CandidateRating =
                  '<Ratings>       
                    <Rating Ratingtype="unknown">       
                      <AppliedKnowledge>3.0</AppliedKnowledge>       
                      <ToolSkills>3.5</ToolSkills>       
                    </Rating>       
                    <Rating Ratingtype="known">       
                      <Experience>9.5</Experience>       
                      <Education>16.0</Education>       
                      <DbDevelopment>4.5</DbDevelopment>       
                    </Rating>
                      <Rating Ratingtype="unknown">       
                      <AppliedKnowledge>4.0</AppliedKnowledge>       
                      <ToolSkills>4.5</ToolSkills>       
                    </Rating>       
                  </Ratings>';
                SELECT * FROM JobCandidates;

My requirement: Assuming this table has thousand records and I need to search for xml column for <Rating Ratingtype="unknown"> <data> </Rating> and replace it with <SuperRating Ratingtype="unknown"> <data> </SuperRating>

Assuming this table has thousand records and I need to search for xml and replace it with — Raj, Mar 28 '16 at 17:29
Welcome to StackOverflow! You can edit the question to complete it, at present state it is unclear what you're asking — Vasfed, Mar 28 '16 at 17:33
Hi Raj, if you want XML to be shown you must format it as code (with back ticks) — Shnugo, Mar 28 '16 at 20:58

score 0 · Answer 1 · edited May 23 '17 at 12:16

EDIT

Regrettfully there's no easy going (at least as far as I know...)

This will first collect all nodes to be "replaced". These nodes will be inserted with the content of the corresponding nodes. Then the original nodes are removed...

Credits to Mikael Eriksson: https://stackoverflow.com/a/15682327/5089204

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowIndex
      ,ROW_NUMBER() OVER(PARTITION BY CandidateId ORDER BY (SELECT NULL)) AS NodeIndex
      ,CandidateId
INTO #tmpNodesToReplace
FROM #JobCandidates
CROSS APPLY CandidateRating.nodes('/Ratings/Rating[@Ratingtype="unknown"]') AS A(B);

DECLARE @I INT=1;
DECLARE @CountNodes INT=(SELECT COUNT(*) FROM #tmpNodesToReplace);
WHILE 1 = 1
BEGIN
    UPDATE #JobCandidates
    SET CandidateRating.modify('insert <SuperRating>{(/Ratings/Rating[@Ratingtype="unknown"])[sql:column("NodeIndex")]/*}</SuperRating> into (/Ratings)[1]')
    FROM #JobCandidates
    INNER JOIN #tmpNodesToReplace ON #JobCandidates.CandidateId = #tmpNodesToReplace.CandidateId
    WHERE #tmpNodesToReplace.RowIndex=@I;

  IF @I>@CountNodes
    BREAK;

  SET @I = @I + 1
END

UPDATE #JobCandidates
SET CandidateRating.modify('delete /Ratings/Rating[@Ratingtype="unknown"]');

CREATE TABLE #JobCandidates(
    CandidateId INT PRIMARY KEY,
    CandidateRating XML NULL);

INSERT INTO #JobCandidates (CandidateId, CandidateRating) VALUES
 (1 -- test row with two "unknown" Ratings
  ,'<Ratings>       
    <Rating Ratingtype="unknown">       
        <AppliedKnowledge>3.0</AppliedKnowledge>       
        <ToolSkills>3.5</ToolSkills>       
    </Rating>       
    <Rating Ratingtype="known">       
        <Experience>9.5</Experience>       
        <Education>16.0</Education>       
        <DbDevelopment>4.5</DbDevelopment>       
    </Rating>
        <Rating Ratingtype="unknown">       
        <AppliedKnowledge>4.0</AppliedKnowledge>       
        <ToolSkills>4.5</ToolSkills>       
    </Rating>       
    </Ratings>')
, (2 --test row with one "unknown" Rating
  ,'<Ratings>       
    <Rating Ratingtype="other than unknown">       
        <AppliedKnowledge>3.0</AppliedKnowledge>       
        <ToolSkills>3.5</ToolSkills>       
    </Rating>       
    <Rating Ratingtype="known">       
        <Experience>9.5</Experience>       
        <Education>16.0</Education>       
        <DbDevelopment>4.5</DbDevelopment>       
    </Rating>
        <Rating Ratingtype="unknown">       
        <AppliedKnowledge>4.0</AppliedKnowledge>       
        <ToolSkills>4.5</ToolSkills>       
    </Rating>       
    </Ratings>')
, (3 --test row with n "unknown" Rating
  ,'<Ratings>       
    <Rating Ratingtype="other than unknown">       
        <AppliedKnowledge>3.0</AppliedKnowledge>       
        <ToolSkills>3.5</ToolSkills>       
    </Rating>       
    <Rating Ratingtype="known">       
        <Experience>9.5</Experience>       
        <Education>16.0</Education>       
        <DbDevelopment>4.5</DbDevelopment>       
    </Rating>
        <Rating Ratingtype="other than unknown">       
        <AppliedKnowledge>4.0</AppliedKnowledge>       
        <ToolSkills>4.5</ToolSkills>       
    </Rating>       
    </Ratings>');

--Find rows with any "unknown" ratings
SELECT * FROM #JobCandidates
WHERE CandidateRating.exist('/Ratings/Rating[@Ratingtype="unknown"]')=1;

--Find all rows with "unknown" (ID=1 showing 2 entries
SELECT CandidateId
      ,A.B.query('.') 
FROM #JobCandidates
CROSS APPLY CandidateRating.nodes('/Ratings/Rating[@Ratingtype="unknown"]') AS A(B);

DROP TABLE #JobCandidates;

Thank u @shnugo.Now i want to just replace the node '' with '' and '' to ' for that particular node — Raj, Mar 28 '16 at 21:34
No that didnt work for me XML before script execution: 3.0 3.5 9.5 16.0 4.5 4.0 4.5 — Raj, Mar 30 '16 at 17:13
@Raj "did not work" is not enough ... What did not work? Errors? Wrong output? What was the expected result with the XML you posted? — Shnugo, Mar 30 '16 at 19:00

score 0 · Answer 2 · answered Apr 11 '16 at 19:54

@shnugo..thnks for inputs. Here is the script i ended up doing my required job. not sure if there is a easier way than this

            CREATE TABLE #JobCandidates(
                CandidateId INT PRIMARY KEY,
                CandidateRating XML NULL);

            INSERT INTO #JobCandidates (CandidateId, CandidateRating) VALUES
             (1 -- test row with two "unknown" Ratings
              ,'<Ratings>       
                <Rating Ratingtype="unknown">       
                    <AppliedKnowledge>3.0</AppliedKnowledge>       
                    <ToolSkills>3.5</ToolSkills>       
                </Rating>       
                <Rating Ratingtype="known">       
                    <Experience>9.5</Experience>       
                    <Education>16.0</Education>       
                    <DbDevelopment>4.5</DbDevelopment>       
                </Rating>
                    <Rating Ratingtype="unknown">       
                    <AppliedKnowledge>4.0</AppliedKnowledge>       
                    <ToolSkills>4.5</ToolSkills>       
                </Rating>       
                </Ratings>')
            , (2 --test row with one "unknown" Rating
              ,'<Ratings>       
                <Rating Ratingtype="other than unknown">       
                    <AppliedKnowledge>3.0</AppliedKnowledge>       
                    <ToolSkills>3.5</ToolSkills>       
                </Rating>       
                <Rating Ratingtype="known">       
                    <Experience>9.5</Experience>       
                    <Education>16.0</Education>       
                    <DbDevelopment>4.5</DbDevelopment>       
                </Rating>
                    <Rating Ratingtype="unknown">       
                    <AppliedKnowledge>4.0</AppliedKnowledge>       
                    <ToolSkills>4.5</ToolSkills>       
                </Rating>       
                </Ratings>')
            , (3 --test row with n "unknown" Rating
              ,'<Ratings>       
                <Rating Ratingtype="other than unknown">       
                    <AppliedKnowledge>3.0</AppliedKnowledge>       
                    <ToolSkills>3.5</ToolSkills>       
                </Rating>       
                <Rating Ratingtype="known">       
                    <Experience>9.5</Experience>       
                    <Education>16.0</Education>       
                    <DbDevelopment>4.5</DbDevelopment>       
                </Rating>
                    <Rating Ratingtype="other than unknown">       
                    <AppliedKnowledge>4.0</AppliedKnowledge>       
                    <ToolSkills>4.5</ToolSkills>       
                </Rating>       
                </Ratings>');

            --Find rows with any "unknown" ratings
            SELECT * FROM #JobCandidates
            WHERE CandidateRating.exist('/Ratings/Rating[@Ratingtype="unknown"]')=1;

            --Find all rows with "unknown" (ID=1 showing 2 entries
            SELECT CandidateId
                  ,A.B.query('.') 
            FROM #JobCandidates
            CROSS APPLY CandidateRating.nodes('/Ratings/Rating[@Ratingtype="unknown"]') AS A(B);

            --change '<Rating Ratingtype="unknown">' to '<SuperRating Ratingtype="unknown">
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowIndex
                  ,ROW_NUMBER() OVER(PARTITION BY CandidateId ORDER BY (SELECT NULL)) AS NodeIndex
                  ,CandidateId
            INTO #tmpNodesToReplace
            FROM #JobCandidates
            CROSS APPLY CandidateRating.nodes('/Ratings/Rating[@Ratingtype="unknown"]') AS A(B);

            DECLARE @I INT=1;
            DECLARE @CountNodes INT=(SELECT COUNT(*) FROM #tmpNodesToReplace);
            WHILE 1 = 1
            BEGIN
             UPDATE #JobCandidates
                SET CandidateRating.modify('insert <SuperRating Ratingtype="unknown">{(/Ratings/Rating[@Ratingtype="unknown"])[sql:column("NodeIndex")]/*}</SuperRating> before (//Rating)[1]')
                FROM #JobCandidates
                INNER JOIN #tmpNodesToReplace ON #JobCandidates.CandidateId = #tmpNodesToReplace.CandidateId
                WHERE #tmpNodesToReplace.RowIndex=@I;



                update #JobCandidates set
                CandidateRating.modify('replace value of (/Ratings/Rating/@Ratingtype)[1] with "delete"')
                FROM #JobCandidates INNER JOIN #tmpNodesToReplace ON #JobCandidates.CandidateId = #tmpNodesToReplace.CandidateId
                WHERE #tmpNodesToReplace.RowIndex=@I;

                UPDATE #JobCandidates
                SET CandidateRating.modify('delete /Ratings/Rating[@Ratingtype="delete"]') FROM #JobCandidates where CandidateRating is not null ;

              IF @I>@CountNodes
                BREAK;

              SET @I = @I + 1
            END

            --drop table #JobCandidates
            --drop table #tmpNodesToReplace
            --select * from #JobCandidates

Isn't this my code actually? Sorry, I do not understand this... Is this the solution you offer to future readers or some code you need help with? — Shnugo, Apr 11 '16 at 19:57

score 0 · Answer 3 · answered Apr 11 '16 at 19:55

Just copy this in a new query window and execute.

There is no easy going... The nodes you want to replace are taken into a temp table. Within a WHILE loop they are inserted as new nodes while the old nodes are removed. XML-DML does not allow more than one operation in one go...

CREATE TABLE #JobCandidates(
    CandidateId INT PRIMARY KEY,
    CandidateRating XML NULL);

INSERT INTO #JobCandidates (CandidateId, CandidateRating) VALUES
 (1 -- test row with two "unknown" Ratings
  ,'<Ratings>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge>3.0</AppliedKnowledge>
    <ToolSkills>3.5</ToolSkills>
  </Rating>
  <Rating Ratingtype="known">
    <Experience>9.5</Experience>
    <Education>16.0</Education>
    <DbDevelopment>4.5</DbDevelopment>
  </Rating>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge>4.0</AppliedKnowledge>
    <ToolSkills>4.5</ToolSkills>
  </Rating>
</Ratings>');

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowIndex
      ,ROW_NUMBER() OVER(PARTITION BY CandidateId ORDER BY (SELECT NULL)) AS NodeIndex
      ,CandidateId
INTO #tmpNodesToReplace
FROM #JobCandidates
CROSS APPLY CandidateRating.nodes('/Ratings/Rating[@Ratingtype="unknown"]') AS A(B);

DECLARE @I INT=1;
DECLARE @CountNodes INT=(SELECT COUNT(*) FROM #tmpNodesToReplace);
WHILE 1 = 1
BEGIN
    UPDATE #JobCandidates
    SET CandidateRating.modify('insert <SuperRating>{(/Ratings/Rating[@Ratingtype="unknown"])[sql:column("NodeIndex")]/*}</SuperRating> into (/Ratings)[1]')
    FROM #JobCandidates
    INNER JOIN #tmpNodesToReplace ON #JobCandidates.CandidateId = #tmpNodesToReplace.CandidateId
    WHERE #tmpNodesToReplace.RowIndex=@I;

  IF @I>@CountNodes
    BREAK;

  SET @I = @I + 1
END

UPDATE #JobCandidates
SET CandidateRating.modify('delete /Ratings/Rating[@Ratingtype="unknown"]');

SELECT * FROM #JobCandidates;

/* Both "Unknown" Ratings are replaced
<Ratings>
  <Rating Ratingtype="known">
    <Experience>9.5</Experience>
    <Education>16.0</Education>
    <DbDevelopment>4.5</DbDevelopment>
  </Rating>
  <SuperRating>
    <AppliedKnowledge>3.0</AppliedKnowledge>
    <ToolSkills>3.5</ToolSkills>
  </SuperRating>
  <SuperRating>
    <AppliedKnowledge>4.0</AppliedKnowledge>
    <ToolSkills>4.5</ToolSkills>
  </SuperRating>
</Ratings>
*/

DROP TABLE #tmpNodesToReplace;
DROP TABLE #JobCandidates;

XML DML Script to replace nodes

3 Answers3

EDIT

previous