XML .modify "where" clause problem

• Simon Brown-346003

  SSC Veteran

  Points: 210

  More actions

  June 17, 2012 at 11:59 am

  #257697

  Hi

  Looking to update xml field where I will have multiple occurrences of the same node name at various levels. What I dont seem able to get is the xpath.

  <Queue ID="1101" Name="Support">

  <Level1 ID="3" Name="1101 - SP3">

  <Level2 ID="24" Name="Simon Level2">

  <Level3 ID="48" Name="Simon Level3 48">

  <Level4 ID="100" Name="1101 - SP3 - Level4 100" Desc="0"/>

  <Level4 ID="101" Name="1101 - SP3 - Level4 101" Desc="1"/>

  </Level3>

  </Level2>

  <Level2 ID="23" Name="Simon Level2 1">

  <Level3 ID="48" Name="Simon Level3 48"><Level4 ID="100" Name="1101 - SP3 - Level4 100" Desc="0"/>

  <Level4 ID="101" Name="1101 - SP3 - Level4 101" Desc="1"/>

  </Level3>

  </Level2>

  </Level1>

  </Queue>

  Highlighted above in bold the level I am trying to update below;

  The exist check below works, so I can do a loop to check if all have been replaced.

  What I am having a problem with is the replace xpath syntax to update the @Name value.

  Pity replace value does not have ability to do all occurrences.

  while (@bExists = 1)

  BEGIN

  -- This xpath check, for more than one attribute fails (note: Works ok with 1 @ID check but that is not unique.

  update outcomeTest set xmltree.modify('replace value of (//Level3[@ID = ("48")] and //Level3[@Name = ("Simon Level3 48")]/@Name)[1] with "Grace Level3"')

  where qid = 100;

  select xmltree from outcomeTest where qid = 100;

  -- This check will work and give me my loop

  select @bExists = xmltree.exist('Level3[@ID = ("48")] and level3[@Name = ("Simon Level3 48")]') from outcomeTest where qid = 100

  END

  The end version will be a stored proc passing the ID and Name values to replace with a new Name.

  For that I think the sql:variable will replace the hard coded example above.

  I may not be looking at this the best way but only been investigating this approach to a problem for 1/2 day.

  If there is an easier way to replace all the occurrences in one pass do please tell

  Hope someone can help or link a decent resource that explains identifying target Node/Attributes better.

  Thanks

• drew.allen

  SSC Guru

  Points: 77011

  More actions

  June 18, 2012 at 8:23 am

  #1502247

  There are several problems with your code.

  First, your "AND" condition isn't correct. You want to modify a node where the same node meets both conditions, but the way that you've specified your and condition, it's possible to have one node meet the first condition and another meet the second condition. That's why it works with the "exist" but not with the "replace". Here is how the "and" should be specified.

  //Level3[@ID = ("48") and @Name = ("Simon Level3 48")]

  Second, you've placed your index on your attribute when it should be on the node. Attributes have to be unique, so it doesn't make sense to have an index on an attribute. The index needs to come before the attribute, not after.

  I've also removed your @bexists formula and just put the expression in the while condition.

  Here is my code:

  DECLARE @x TABLE( x XML )

  INSERT @x

  VALUES( '

  <Queue ID="1101" Name="Support">

  <Level1 ID="3" Name="1101 - SP3">

  <Level2 ID="24" Name="Simon Level2">

  <Level3 ID="48" Name="Simon Level3 48">

  <Level4 ID="100" Name="1101 - SP3 - Level4 100" Desc="0"/>

  <Level4 ID="101" Name="1101 - SP3 - Level4 101" Desc="1"/>

  </Level3>

  </Level2>

  <Level2 ID="23" Name="Simon Level2 1">

  <Level3 ID="48" Name="Simon Level3 48">

  <Level4 ID="100" Name="1101 - SP3 - Level4 100" Desc="0"/>

  <Level4 ID="101" Name="1101 - SP3 - Level4 101" Desc="1"/>

  </Level3>

  </Level2>

  </Level1>

  </Queue>

  ' )

  WHILE EXISTS ( SELECT x FROM @x WHERE x.exist('//Level3[@ID = ("48") and @Name = ("Simon Level3 48")]' ) = 1 )

  UPDATE @x

  SET x.modify('replace value of (//Level3[@ID = ("48") and @Name = ("Simon Level3 48")])[1]/@Name with "Grace Level3"')

  SELECT x

  FROM @x

  Drew

  WHILE EXISTS ( SELECT x FROM @x WHERE x.exist('//Level3[@ID = ("48") and @Name = ("Simon Level3 48")]' ) = 1 )

  UPDATE @x

  SET x.modify('replace value of (//Level3[@ID = ("48") and @Name = ("Simon Level3 48")])[1]/@Name with "Grace Level3"')

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Simon Brown-346003

  SSC Veteran

  Points: 210

  More actions

  June 19, 2012 at 3:19 am

  #1502641

  Thanks very much for looking at this and providing the solution and explaination for my mistakes.

  Only had a couple hours reading up on XML in SQL so I appreciate the feedback.

  That worked a charm, now I will look to do same with some sql:variable replacements in a stored procedure.

  Thanks again

  Simon