Modifying XML with into or after

• pitioageneral

  SSC Enthusiast

  Points: 139

  More actions

  May 6, 2016 at 4:02 am

  #104268

  The following xml is saved as XML Data Type within the sql server.

  <row id="1000000" xml:space="preserve">

  <c1>Exported</c1>

  <c2>Text Sample 1</c2>

  <c2 m="2">Text Sample 2</c2>

  <c2 m="3">Text Sample 3</c2>

  <c2 m="4">Text Sample 4</c2>

  <c3>Text Sample 1</c3>

  <c3 m="2">Text Sample 2</c3>

  <c3 m="3">Text Sample 3</c3>

  <c4>Text Sample</c4>

  <c5>Text Sample</c5>

  </row>

  I want to add an extra element (ctest) after the c3 element.BUT after the last c3 element.

  Xml should looks like the following :

  <row id="1000000" xml:space="preserve">

  <c1>Exported</c1>

  <c2>Text Sample 1</c2>

  <c2 m="2">Text Sample 2</c2>

  <c2 m="3">Text Sample 3</c2>

  <c2 m="4">Text Sample 4</c2>

  <c3>Text Sample 1</c3>

  <c3 m="2">Text Sample 2</c3>

  <c3 m="3">Text Sample 3</c3>

  <ctest>New Added</ctest>

  <c4>Text Sample</c4>

  <c5>Text Sample</c5>

  </row>

  Please help!!

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 15, 2016 at 8:32 pm

  #1879160

  Sometimes XML questions get no love around here.

  I am not great with the XML modify method (something I need to read up on and practice I guess) but here's an approach: we can cast the XML as a string and STUFF your row where required as shown here:

  -- sample table containing your xml

  DECLARE @table TABLE(id int identity, xcol xml);

  INSERT @table(xcol) VALUES(

  '<row id="1000000" xml:space="preserve">

  <c1>Exported</c1>

  <c2>Text Sample 1</c2>

  <c2 m="2">Text Sample 2</c2>

  <c2 m="3">Text Sample 3</c2>

  <c2 m="4">Text Sample 4</c2>

  <c3>Text Sample 1</c3>

  <c3 m="2">Text Sample 2</c3>

  <c3 m="3">Text Sample 3</c3>

  <c4>Text Sample</c4>

  <c5>Text Sample</c5>

  </row>');

  --Solution

  SELECT id,

  xcol =

  CAST(

  REPLACE(REPLACE(STUFF(

  CAST(xcol as varchar(8000)),

  CHARINDEX('<c4>',CAST(xcol as varchar(8000)))-1,0,'<ctest>New Added</ctest>'),'&#x0A',''),'>;<','><')

  AS xml)

  FROM @table;

  Another approach would be to use mdq.xmltransform if you are familiar with XSLT.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Mikael Eriksson SE

  SSCommitted

  Points: 1706

  More actions

  June 15, 2016 at 3:03 am

  #1885102

  declare @X xml = '

  <row id="1000000" xml:space="preserve">

  <c1>Exported</c1>

  <c2>Text Sample 1</c2>

  <c2 m="2">Text Sample 2</c2>

  <c2 m="3">Text Sample 3</c2>

  <c2 m="4">Text Sample 4</c2>

  <c3>Text Sample 1</c3>

  <c3 m="2">Text Sample 2</c3>

  <c3 m="3">Text Sample 3</c3>

  <c4>Text Sample</c4>

  <c5>Text Sample</c5>

  </row>';

  set @X.modify('insert <ctest>New Added</ctest> after (row/c3)[last()]');

• durga.palepu

  Hall of Fame

  Points: 3530

  More actions

  July 9, 2016 at 6:29 am

  #1889287

  Useful tips.

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  July 10, 2016 at 4:42 am

  #1889327

  Quick thought, only one modification can be done with modify at the time, therefore it may be simpler to use either a query or FLWOR (For, Let, Where, Order by, Return) for multiple inserts.

  😎

  Here is a simple example of multiple modifications using query

  DECLARE @OUTPUT XML;

  DECLARE @X XML = '

  <row id="1000000" xml:space="preserve">

  <c1>Exported</c1>

  <c2>Text Sample 1</c2>

  <c2 m="2">Text Sample 2</c2>

  <c2 m="3">Text Sample 3</c2>

  <c2 m="4">Text Sample 4</c2>

  <c3>Text Sample 1</c3>

  <c3 m="2">Text Sample 2</c3>

  <c3 m="3">Text Sample 3</c3>

  <c4>Text Sample</c4>

  <c5>Text Sample</c5>

  <t1>Text Sample</t1>

  </row>';

  SELECT @OUTPUT = @X.query('

  <row id="1000000" xml:space="preserve">

  <ctest>Added before the first element</ctest>

  { /row/c1 }

  <ctest>Added after c1 and before c2</ctest>

  { /row/c2 }

  <ctest>Added after c2 and before c3</ctest>

  { /row/c3 }

  <ctest>Added after c3 and before c4</ctest>

  { /row/c4 }

  <ctest>Added after c4 and before t1</ctest>

  { /row/t1 }

  <ctest>Added after the last element</ctest>

  </row>

  ');

  SELECT @OUTPUT;

  Ouput

  <row id="1000000" xml:space="preserve">

  <ctest>Added before the first element</ctest>

  <c1>Exported</c1>

  <ctest>Added after c1 and before c2</ctest>

  <c2>Text Sample 1</c2>

  <c2 m="2">Text Sample 2</c2>

  <c2 m="3">Text Sample 3</c2>

  <c2 m="4">Text Sample 4</c2>

  <ctest>Added after c2 and before c3</ctest>

  <c3>Text Sample 1</c3>

  <c3 m="2">Text Sample 2</c3>

  <c3 m="3">Text Sample 3</c3>

  <ctest>Added after c3 and before c4</ctest>

  <c4>Text Sample</c4>

  <ctest>Added after c4 and before t1</ctest>

  <t1>Text Sample</t1>

  <ctest>Added after the last element</ctest>

  </row>