TSQL for xml add schema attribute to root node

Question

My situation is this (simplified):

DECLARE @period XML = (
SELECT
'2012' 'period'
FOR XML PATH(''), ROOT ('survey'))

DECLARE @persons XML = (
SELECT
Person.Name 'users/person'
FROM Person
FOR XML PATH(''), ROOT ('company'))

SET @persons.modify('insert sql:variable("@period") as first into (/company)[1]')
SELECT @persons

Which gives me a XML like this:

<company>
  <survey>
    <period>2012</period>
  </survey>
  <users>
    <person>Dubach</person>
  </users>
  <users>
    <person>Pletscher</person>
  </users>
  ...

Now I need to add a XML-schema to the root node like this:

<company xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mydomain.com/xmlns/bla/blabla/myschema.xsd" xmlns="http://www.mydomain.com/xmlns/bla/blabla">
  <survey>
    <period>2012</period>
  </survey>
  <users>
    <person>Dubach</person>
  </users>
  <users>
    <person>Pletscher</person>
  </users>
  ...

Microsoft says I have to use WITH XMLNAMESPACES before the SELECT statement but that does not work in my case.

How can I add these xmlnamespaces?

podiluska · Answer 1 · 2012-10-04T13:02:19.687

4

Split the declare from the select, then use with xmlnamespaces as described.

DECLARE @persons XML 

;with xmlnamespaces (
    'http://www.mydomain.com/xmlns/bla/blabla' as ns,
    'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
select @persons = (
    select
        Person.Name as 'ns:users/person' 
        FROM Person 
    FOR XML PATH(''), ROOT ('company')
)

set @persons.modify('insert ( attribute xsi:schemaLocation {"http://www.mydomain.com/xmlns/bla/blabla/myschema.xsd"}) into (/company)[1]')

edited Oct 04 '12 at 13:02

answered Oct 04 '12 at 11:46

podiluska

50,950
7
98
104

Thank you for your answer. It works for one namespace. How can I add three namespaces as descripted in my post? – Bruno Bieri Oct 04 '12 at 11:51
Thanks, how can I change the "schemaLocation" prefix? I found that I can use "DEFAULT" for the default "xmlns" but how can I create the "xsi:schemaLocation" ? – Bruno Bieri Oct 04 '12 at 12:05
Using a `modify`... see above – podiluska Oct 04 '12 at 13:02
The attribute does not appear in my case. At the moment I can't see the problem. I found another solution. See my answer. – Bruno Bieri Oct 04 '12 at 13:51

score 3 · Accepted Answer · edited May 23 '17 at 10:29

I found a solution to add all the namespaces here:

https://stackoverflow.com/a/536781/1306012

Obvious it's not very "nice" style but in my case it works and I haven't found another working solution yet.

SOLUTION

DECLARE @period XML = (
SELECT
'2012' 'period'
FOR XML PATH(''), ROOT ('survey'))

DECLARE @persons XML = (
SELECT
Person.Name 'users/person'
FROM Person
FOR XML PATH(''), ROOT ('company'))

SET @persons.modify('insert sql:variable("@period") as first into (/company)[1]')

-- SOLUTION
SET @persons = replace(cast(@persons as varchar(max)), '<company>', '<company xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mydomain.com/xmlns/bla/blabla/myschema.xsd" xmlns="">')


SELECT @persons

TSQL for xml add schema attribute to root node

2 Answers2

Linked