問題描述

我有一個關于使用 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 查詢，其中:

1. 一個命名空間只在根元素中定義，并且

2. 根元素具有命名空間定義以外的數據屬性，并且

3. 在子元素的屬性名稱中使用了對命名空間的引用?

我查看了 如何在使用 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:

1. a namespace is defined only in the root element, and

2. the root element has data attributes other than namespace definitions, and

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

這篇關于T-SQL FOR XML 查詢中的命名空間問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！