TSQL for xml 添加模式属性到根节点 [英] TSQL for xml add schema attribute to root node

问题描述

我的情况是这样(简化):

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

这给了我这样的 XML:

Which gives me a XML like this:

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

现在我需要像这样向根节点添加一个 XML 模式:

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>
  ...

微软说我必须使用 WITH XMLNAMESPACES 在 SELECT 语句之前，但这在我的情况下不起作用.

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

如何添加这些 xml 命名空间?

How can I add these xmlnamespaces?

推荐答案

我在这里找到了添加所有命名空间的解决方案:

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.

解决方案

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 添加模式属性到根节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！