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?