question

EdMcD avatar image
EdMcD asked

When modifying xml using XML.MODIFY how do you add namespace to a sql:variable

I have a table with a XML column. The XML in the column is:

<MyXML xmlns="http://MyNameSpace">
   <Assignment id="1095">
       <Test />
   </Assignment>
</MyXML>

I then can update the XML like this:

UPDATE @TestTable
SET XMLData.modify('declare namespace MTW="http://MyNameSpace";insert <MTW:Attachments/> into (/MTW:MyXML/MTW:Assignment)[1]')

Result:
<MyXML xmlns="http://MyNameSpace">
  <Assignment id="1095">
    <Test />
    <Attachments />
  </Assignment>
</MyXML>

Next, I create an XML fragment and store it in a XML data type variable and update the XML in the table column.

DECLARE @AssignmentAttachmentsXML AS XML
SET @AssignmentAttachmentsXML = '<Attachments/>'

UPDATE @TestTable
SET XMLData.modify('declare namespace MTW="http://MyNameSpace";insert sql:variable("@AssignmentAttachmentsXML") into (/MTW:MyXML/MTW:Assignment)[1]')

Result:

<MyXML xmlns="http://MyNameSpace">
  <Assignment id="1095">
    <Test />
    <Attachments xmlns="" />
  </Assignment>
</MyXML>

The result from this is the fragment has been added to the column XML but contains an empty string for the namespace.

If I add the namespace alias to the XML variable I get an error

SET @AssignmentAttachmentsXML = '<MTW:Attachments/>'
XML parsing: line 1, character 18, undeclared prefix

How do I add a namespace to a sql:variable?

tsqlxmlsql xml
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.