Question

审议以下文件:

;WITH XMLNAMESPACES(DEFAULT  http://www.mynamespace.co.uk )
,CTE_DummyData AS (
    select id=1
)
select TOP 1
    [@ID]=1,
    (select top 1 [@ID] = 1 FROM CTE_DummyData FOR XML PATH ( Child ), TYPE)
from CTE_DummyData
FOR XML PATH ( Parent )

Thie 返回Xml:

<Parent xmlns="http://www.mynamespace.co.uk" ID="1">
  <Child xmlns="http://www.mynamespace.co.uk" ID="1" />
</Parent>

我需要的只是把Xml和xmlns声明归还给根本内容。指称:

<Parent xmlns="http://www.mynamespace.co.uk" ID="1">
  <Child ID="1" />
</Parent>

是否有办法这样做?

Note: The above SQL is an extreme simplification of the actual code, which produces a complex document, so changing from FOR XML PATH isn t really an option without giving me a couple of days of extra work to do. To be clear on the the top level of the entire document is required to have the NS, and all children should be without.

Answer 1

您可使用query from hell"。

;with CTE_DummyData AS (
    select id=1
)
select 1 as tag,
       0 as parent,
        http://www.mynamespace.co.uk  as [Parent!1!xmlns],
       id as [Parent!1!ID],
       null as [Child!2!ID]
from CTE_DummyData       
union all
select top 1
       2,
       1,
       null,
       null,
       id
from CTE_DummyData       
for xml explicit

Answer 2

你们可以利用UDF生产儿童节点。 E.g.

ALTER FUNCTION [dbo].[udf_get_child_section] (
    @serviceHeaderId INT
 )
RETURNS XML

BEGIN

    DECLARE @result XML;

    SELECT @result = 
    (
        SELECT 1 AS  ChildElement 
        FOR XML PATH( Child )
    )

    RETURN @result

END


GO

DECLARE @Ids TABLE
( 
    ID int 
)

INSERT INTO @Ids
SELECT 1 AS ID 
UNION ALL
SELECT 2 AS ID

;WITH XMLNAMESPACES (DEFAULT  http://www...com/content )
SELECT 
    [dbo].[udf_get_child_section](ID)
FROM 
    @Ids
FOR XML PATH( Parent )

友情链接