Unable to insert new node using insert (XML DML)

Question

I am trying to insert a node in XML using XML insert (XML DML).

The XML looks like this:

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <Worksheet xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ss:Name="1">
        <Table xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">Audit ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">Audit Subcategory ID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">1</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">ObjectID</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">ObjectTypeID</Data>
                </Cell>
            </Row>
            <Row xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">55406</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">3</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">1</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">6078</Data>
                </Cell>
                <Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
                    <Data ss:Type="String">1</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>

I am trying to insert a node using the code below:

SET @xml.modify('insert <Maintenance>111111111111111</Maintenance> into (/Workbook)[1]');

and then I display the data using

  Select @xml;

The problem is the new node is not getting displayed. I try to modify the XML using

SET @xml.modify('insert <Maintenance>111111111111111</Maintenance> into (/Workbook/Worksheet)[1]');

But this also doesn't insert any node.

Can any one please suggest what I may be doing wrong?

score 2 · Answer 1 · answered Dec 08 '15 at 17:43

2

Looks like the default namespace needs to be used here when inserting. Try this.

set @xml.modify('
declare namespace ns="urn:schemas-microsoft-com:office:spreadsheet";
insert <ns:Maintenance>111111111111111</ns:Maintenance>
into (/ns:Workbook)[1]');


select @xml

answered Dec 08 '15 at 17:43

Kik

428
3
9

@NimbleFungus That is odd, as it is working for me, it appears underneath the Worksheet element. I will try to get it up in a sql fiddle. – Kik Dec 08 '15 at 17:50
Please see my answer. I am using SQL Server 2008 R2. Can version affect the behaviour? But thanks a lot for you help. You answer game me idea to try my answer. – Nimble Fungus Dec 08 '15 at 17:58

score 0 · Accepted Answer · answered Dec 08 '15 at 17:54

This Worked guys..

 SET @xml.modify('
        declare default element namespace  "urn:schemas-microsoft-com:office:spreadsheet";
        declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet" ;
        declare namespace x="urn:schemas-microsoft-com:office:excel";
        insert sql:variable("@xmlStyle") as first into (/Workbook)[1]')

I had to declare all the name spaces that were in use in the XML. The @xmlStyle is of XML Type and contains the fragment of XML that i want to include as node.

@xmlStyle AS XML

Unable to insert new node using insert (XML DML)

2 Answers2