T-SQL FOR XML 查询中的命名空间问题

2021-10-02 00:00:00 xml-namespaces xml sql-server for-xml

我有一个关于使用 SQL Server FOR XML 创建带有命名空间的 XML 文档的问题.我需要创建一个像下面这样的文档，其中根元素中有一个命名空间定义(xmlns:ijk="h:/ijk")，以及一个子元素属性中的命名空间引用(ijk:LMNO="3333").

我正在生成要 POST 到 Web 服务的 XML:

<CHILD CDEF="2222" ijk:LMNO="3333"/>

在我开始使用命名空间之前，我编写了第一个示例查询来获得正确的结构，结果正是预期的.

SELECT[RSTU] = 1111,(选择[CDEF] = 2222，[LMNO] = 3333FOR XML RAW('CHILD'), TYPE)FOR XML RAW('ROOT'), TYPE;

结果

<儿童 CDEF="2222" LMNO="3333"/>

然后我尝试使用 WITH NAMESPACES 添加命名空间，但 SQL Server 被带走并将所有命名空间添加到所有元素.目标 Web 服务不接受具有命名空间重载的 XML(在我的真实案例中，有四层元素和三个命名空间，这真是一团糟).

WITH XMLNAMESPACES('h:/i.j.k' 作为 ijk，'h:/x.y.z' 作为 xyz)选择[RSTU] = 1111,(选择[CDEF] = 2222, [ijk:LMNO] = 3333FOR XML RAW('CHILD'), TYPE)FOR XML RAW('ROOT'), TYPE;

结果:

<孩子 xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" CDEF="2222" ijk:LMNO="3333"/>

我在 Books Online 中读到，虽然不推荐，但可以像常规属性一样添加命名空间.我试过了，它生成了正确的命名空间定义:

SELECT[xmlns:ijk] = 'h:/i.j.k',[RSTU] = 1111,(选择[CDEF] = 2222，[LMNO] = 3333FOR XML RAW('CHILD'), TYPE)FOR XML RAW('ROOT'), TYPE;

结果:

<儿童 CDEF="2222" LMNO="3333"/>

上面的输出有一个很好看的命名空间定义，但 LMNO 属性没有其必需的 ijk: 命名空间引用前缀.我尝试添加命名空间引用，但出现错误:

SELECT[xmlns:ijk] = 'h:/i.j.k',[RSTU] = 1111,(选择[CDEF] = 2222, [ijk:LMNO] = 3333FOR XML RAW('CHILD'), TYPE)FOR XML RAW('ROOT'), TYPE;

结果:

<块引用>

消息 6846，级别 16，状态 2，第 34 行
FOR XML 列名称ijk:LMNO"缺少 XML 名称空间前缀ijk"声明.

是否可以编写生成 XML 的 T-SQL FOR XML 查询，其中:

一个命名空间只在根元素中定义，并且
根元素具有命名空间定义以外的数据属性，并且
在子元素的属性名称中使用了对命名空间的引用?

我查看了如何在使用 FOR XML PATH 时删除嵌套查询中的冗余命名空间.在本主题中，根元素只有命名空间定义，没有数据属性.

解决方案

这很丑陋，但可以解决

SELECT铸造(替换(铸造((选择[xmlns:xyz] = 'h:/x.y.z',[xmlns:ijk] = 'h:/i.j.k',[RSTU] = 1111,(选择[@CDEF] = 2222，[@ns_ijk_LMNO] = 3333FOR XML PATH('ROOT'), TYPE)FOR XML RAW('CHILD'), TYPE) AS NVARCHAR(MAX)),'ns_ijk_','ijk:') AS XML);

结果

<ROOT CDEF="2222" ijk:LMNO="3333"/></孩子>

通过对外部 SELECT 使用 RAW 模式，允许放置命名空间声明，就像它们是属性一样.

内部 FOR XML PATH 不会使用这些命名空间(WITH XMLNAMESPACES 的其他行为！)，但不可能在那里使用命名空间前缀.>

所以我在属性名称中添加了一些东西，将整个 XML 转换为 NVARCHAR(MAX)，替换我的虚拟对象并将其转换回来.

请前往连接问题并投票.这真的很烦人！

重复的命名空间(使用子选择时)没有错，但会使输出膨胀，并且可能会在验证器中发生冲突.

I have a question about using SQL Server FOR XML to create XML documents with namespaces. I need to create a document like below where there is a namespace definition in the root element (xmlns:ijk="h:/i.j.k"), and a namespace reference in a child element attribute (ijk:LMNO="3333").

I am generating the XML to POST to a Web service:

<ROOT xmlns:ijk="h:/i.j.k" RSTU="1111">
  <CHILD CDEF="2222" ijk:LMNO="3333" />
</ROOT>

I wrote this first sample query to get the structure correct before I started fighting with namespaces, and the result is what is expected.

SELECT
    [RSTU] = 1111,
    (SELECT
         [CDEF] = 2222, [LMNO] = 3333
     FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result

<ROOT RSTU="1111">
  <CHILD CDEF="2222" LMNO="3333" />
</ROOT>

Then I tried using WITH NAMESPACES to add the namespaces, but SQL Server gets carried away and adds all namespaces to all elements. The target Web service does not accept the XML with the namespace overload (in my real case, there are four levels of elements and three namespaces, and it makes a real mess).

WITH XMLNAMESPACES ('h:/i.j.k' as ijk, 'h:/x.y.z' as xyz)
SELECT
  [RSTU] = 1111,
  (SELECT
       [CDEF] = 2222, [ijk:LMNO] = 3333
  FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result:

<ROOT xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" RSTU="1111">
    <CHILD xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" CDEF="2222" ijk:LMNO="3333" />
</ROOT>

I read in Books Online that, while not recommended, namespaces can be added like a regular attribute. I tried this, and it generated the proper namespace definitions:

SELECT
    [xmlns:ijk] = 'h:/i.j.k',
    [RSTU] = 1111, 
    (SELECT
         [CDEF] = 2222, [LMNO] = 3333
     FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result:

<ROOT xmlns:ijk="h:/i.j.k" RSTU="1111">
  <CHILD CDEF="2222" LMNO="3333" />
</ROOT>

The output above has a good looking namespace definition, but the LMNO attribute does not have its required ijk: namespace reference prefix. I tried adding the namespace reference, but I got an error:

SELECT
    [xmlns:ijk] = 'h:/i.j.k',
    [RSTU] = 1111,
    (SELECT
         [CDEF] = 2222, [ijk:LMNO] = 3333
     FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result:

Msg 6846, Level 16, State 2, Line 34
XML name space prefix 'ijk' declaration is missing for FOR XML column name 'ijk:LMNO'.

Is it possible to write a T-SQL FOR XML query that generates XML where:

a namespace is defined only in the root element, and
the root element has data attributes other than namespace definitions, and
references to the namespace are used in attribute names in child elements?

I reviewed How do I remove redundant namespace in nested query when using FOR XML PATH. In this topic the root element has only namespace definitions and no data attributes.

解决方案

This is ugly, but a workaround

SELECT
    CAST(REPLACE(CAST(
    (SELECT
    [xmlns:xyz] = 'h:/x.y.z',
    [xmlns:ijk] = 'h:/i.j.k',
    [RSTU] = 1111,
    (SELECT
         [@CDEF] = 2222, [@ns_ijk_LMNO] = 3333
     FOR XML PATH('ROOT'), TYPE)
     FOR XML RAW('CHILD'), TYPE) AS NVARCHAR(MAX)),'ns_ijk_','ijk:') AS XML);

The result

<CHILD xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" RSTU="1111">
  <ROOT CDEF="2222" ijk:LMNO="3333" />
</CHILD>

By using the RAW mode for the outer SELECT it is allowed to place namespace declarations just as if they were attributes.

The internal FOR XML PATH will not use these namespaces (other behavior with WITH XMLNAMESPACES!), but it is not possible to use a namespace prefix there.

So I add something to the attributes name, cast the whole XML to NVARCHAR(MAX), replace my dummy and cast it back.

Please go to the connect issue and vote. This is really annoying!

The repeated namespaces (when using sub-selects) are not wrong but bloating the output and can clash in validators.

相关文章