May 6, 2016 at 4:02 am
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!!
May 15, 2016 at 8:32 pm
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>'),'
',''),'>;<','><')
AS xml)
FROM @table;
Another approach would be to use mdq.xmltransform if you are familiar with XSLT.
-- Itzik Ben-Gan 2001
June 15, 2016 at 3:03 am
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()]');
July 9, 2016 at 6:29 am
Useful tips.
July 10, 2016 at 4:42 am
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>
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy