SQL Server modify XML to add namespace declaration

Question

I have a table with a column full of untyped xml, which I need to copy to a companion database, then manipulate and use. I decided that it would be best to make an XML schema collection on the companion database and use that to give the companion XML column a type, allowing swifter use of the XML and a level of validation. I will need to copy data from the untyped column on a regular basis.

My problem is that the untyped xml does not contain a namespace nor version; my understanding is that I need to add a namespace declaration and version if I wish to insert the untyped xml into my typed column and validate it. What is the best way to do this (best here means quickest reliable method for large numbers of rows)?

At the moment all I have that works is to convert the xml to a varchar, then use STUFF() to insert the namespace declaration etc and convert the result back to xml, which seems a bit hooky. I have tried using modify() but have not so far been able to find the correct syntax (inserting an attribute called "xmlns" gives an error and using .modify('declare default element namespace "http://tempuri.org/QAnswers.xsd";') seems to require a following xquery or something...

here's an example:

DECLARE @Table TABLE (Id INT IDENTITY(1,1), Answers XML)
INSERT @Table
SELECT
 N'<questionnaire-answers type="Medical">
  <title lang="en">New Thing</title>
  <sections>
    <section id="4684f484-080a-4ac7-90cb-f8064a0ea1ae">
      <title lang="en">First SubThing</title>
      <description lang="en">MyThingDescription
      </description>
      <footer lang="en">PLEASE LOG CLEARLY </footer>
      <elements>
        <question-freetext id="d0a75e8f-9856-4eb4-9187-b112cb2e5edd" name="Thing Info" value="Something Happened">
          <text lang="en" />
        </question-freetext>
      </elements>
    </section>
  </sections>
</questionnaire-answers>'

-- SOME ATTEMPTS --

--DECLARE @NS VARCHAR(50) = 'http://tempuri.org/QAnswers.xsd'
--UPDATE @Table
--SET Answers.modify('declare default element namespace "http://tempuri.org/QAnswers.xsd";')
--SET Answers.modify('insert attribute xmlns {sql:variable("@NS")} into (/questionnaire-answers)[1]')

-- The only thing I have been able to make work
UPDATE @Table
    SET Answers = 
    STUFF(CONVERT(VARCHAR(MAX),Answers),1,23,'<?xml version ="1.0"?>
     <questionnaire-answers xmlns ="http://tempuri.org/QAnswers.xsd" ')

The outcome I need will look something like this (the version doesn't display apparently, but it's in there!):

<questionnaire-answers xmlns="http://tempuri.org/QAnswers.xsd" type="Medical">
  <title lang="en">New Thing</title>
  <sections>
    <section id="4684f484-080a-4ac7-90cb-f8064a0ea1ae">
      <title lang="en">First SubThing</title>
      <description lang="en">MyThingDescription
      </description>
      <footer lang="en">PLEASE LOG CLEARLY </footer>
      <elements>
        <question-freetext id="d0a75e8f-9856-4eb4-9187-b112cb2e5edd" name="Thing Info" value="Something Happened">
          <text lang="en" />
        </question-freetext>
      </elements>
    </section>
  </sections>
</questionnaire-answers>

See http://stackoverflow.com/questions/9002403/how-to-add-xml-encoding-xml-version-1-0-encoding-utf-8-to-xml-output-in — Brian Pressler, Jul 02 '15 at 13:45
@BrianPressler - I saw that one, but it mentions using the convert to `VARCHAR` approach that I use above. I was hoping there was some better way to get the namespace declaration and version into the XML - these are contained in the XML schema collection, unlike the encoding. — High Plains Grifter, Jul 02 '15 at 14:12
The accepted answer there seems to be your answer here. You have to add it manually. — Brian Pressler, Jul 02 '15 at 14:16
Yeah, well if there really is no option :-/ It just takes ages and seems a bit non-ideal that the xml cannot be directly changed, but has to be inefficiently converted and un-converted in this way. I guess I just hoped it would be neater than this; it feels like a workaround, particularly given that while using modify, you can apparently declare default namespaces... — High Plains Grifter, Jul 02 '15 at 14:24
Not without string concatenation. As soon as you convert to XML, SQL server stores it as ucs-2 and the XML declaration () and attributes are stripped by design. — Brian Pressler, Jul 02 '15 at 15:28

SQL Server modify XML to add namespace declaration

0 Answers0