SQL Server 子节点中的空白 xml 命名空间 [英] Blank xml namespace in child nodes in SQL Server

问题描述

我需要有关 sql 中以下内容的帮助:

Hi i need help with the following in sql:

我需要以这种格式创建一个 xml 文件

I need to create a xml file in this format

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
 <FIToFIPmtStsRpt>
  <GrpHdr>
    <MsgId></MsgId>
  </GrpHdr>
  <OrgnlGrpInfAndSts>
    <OrgnlMsgId />
  </OrgnlGrpInfAndSts>
 </FIToFIPmtStsRpt>
</document>

目前我有一个保存主要信息的变量，我建立了之间的信息(假设 grphdr 可以多次插入主 xml，具有不同的信息)

at the moment i have a variable that holds the main info and i build up the between info (take it that grphdr can be inserted multiple times back into the main xml, with different info)

declare @xml xml='<Document
xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
<FIToFIPmtStsRpt>
</FIToFIPmtStsRpt>
</Document>
'

declare @xmlgrp xml='<GrpHdr>
  <MsgId></MsgId>
</GrpHdr>'
--here i do some code to fill msgid

然后当我将 grphdr 添加回主 xml

then when i add the grphdr back into the main xml

SET @xml.modify
('declare namespace a= "urn:iso:std:iso:20022:tech:xsd:001.002.001.04";
insert sql:variable("@xmlgrp") 
into (a:Document/a:FIToFIPmtStsRpt)[1]')

select @xml

我需要找出顶级文件格式，但现在发生的是以下内容

i need to get out the top file format but what happens now is the following is given

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
 <FIToFIPmtStsRpt>
   <GrpHdr xmlns="">-- i need this xmlns tag out
     <MsgId />
   </GrpHdr>
 </FIToFIPmtStsRpt>
 </Document>

不知何故，我需要 xml 中的空 xmlns 标记.我无法转换为 varchar(max) 来删除，因为我们的数据库已将变量限制为 8000 个字符而我的xml可以增长到8000多个.1 个文件中可以有多个 grphdr 或 OrgnlGrpIn​​fAndSts

somehow i need the empty xmlns tag out of the xml. I can't convert to varchar(max) to remove as our db has limited the variable to 8000 characters and my xml can grow to more than 8000. There can be multiple grphdr or OrgnlGrpInfAndSts in 1 file

table: lim_Live_Inbound
lim_msg_id                    |  lim_request_transaction_id  | client_name
------------------------------------------------------
021/00210006/20160225/000002  | 00012016-02-25000000023      | Mr Piet
021/00210006/20160225/000002  | 00012016-02-25000000022      | Mrs Name

必须像这样生成

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
 <FIToFIPmtStsRpt>
  <GrpHdr>
    <MsgId>021/00210006/20160225/000002</MsgId>
  </GrpHdr>
  <OrgnlGrpInfAndSts>
    <OrgnlMsgId>00012016-02-25000000023</OrgnlMsgId>
    <name>Mr Piet</name>
  </OrgnlGrpInfAndSts>
  <OrgnlGrpInfAndSts>
    <OrgnlMsgId>00012016-02-25000000022</OrgnlMsgId>
    <name>Mrs Name</name>
  </OrgnlGrpInfAndSts>
 </FIToFIPmtStsRpt>
</document> 

这就是我尝试以 xml 方式插入的原因.如果有人能以更好的方式帮助我，我将不胜感激.

this is why i'm trying the insert into xml way. If any one can help me with a better way it would be much appreciated.

推荐答案

EDIT 2:我终于找到了避免重复命名空间的方法.首先创建没有命名空间的嵌套 XML，然后加入它:

更新(2017 年 12 月)

这种解决方法实际上仍然没有帮助.命名空间 xmlns="" 被视为 * 中的所有内容不在命名空间内... 您可以将结果转换为 NVARCHAR(MAX) 并使用 REPLACE 去掉 xmlns="".然后您可以将字符串重新转换为 XML.微软的耻辱，10(！！)岁的问题(见下面的链接)仍未解决.请去那里投票！

EDIT 2: I finally found a way to avoid repeated namespaces. First you create the nested XML without the namespace, then you join it:

UPDATE (Dec 2017)

This workaround is still not really helpfull actually. The namespaces xmlns="" are taken as *everything inside is not within a namespace... You might convert the result to NVARCHAR(MAX) and use REPLACE to get rid of xmlns="". Then you can re-convert the string to XML. Shame on Microsoft, that the 10(!!) years old issue (see link below) is still unsolved. Please go there and vote!

DECLARE @lim_Live_Inbound TABLE(lim_msg_id VARCHAR(100),lim_request_transaction_id VARCHAR(100),client_name VARCHAR(100));
INSERT INTO @lim_Live_Inbound VALUES
 ('021/00210006/20160225/000002','00012016-02-25000000023','Mr Piet')
,('021/00210006/20160225/000002','00012016-02-25000000022','Mrs Name');

DECLARE @nestedXMLs TABLE(MsgId VARCHAR(100),nestedXML XML);

WITH GrpMsg AS
(
    SELECT DISTINCT lim_msg_id AS MsgId
    FROM @lim_Live_Inbound
)
INSERT INTO @nestedXMLs 
SELECT MsgId 
     ,(
        SELECT innerTbl.lim_request_transaction_id AS OrgnlMsgId
              ,innerTbl.client_name AS name
        FROM @lim_Live_Inbound AS innerTbl
        WHERE innerTbl.lim_msg_id=GrpMsg.MsgId
        FOR XML PATH('OrgnlGrpInfAndSts'),TYPE
      ) 
FROM GrpMsg;

WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04')
,GrpMsg AS
(
    SELECT DISTINCT lim_msg_id AS MsgId
    FROM @lim_Live_Inbound
)
SELECT GrpMsg.MsgId AS [GrpHdr/MsgId]
      ,n.nestedXML AS [node()]
FROM GrpMsg
INNER JOIN @nestedXMLs AS n ON GrpMsg.MsgId=n.MsgId
FOR XML PATH('FIToFIPmtStsRp'),ROOT('Document')

结果

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
  <FIToFIPmtStsRp>
    <GrpHdr>
      <MsgId>021/00210006/20160225/000002</MsgId>
    </GrpHdr>
    <OrgnlGrpInfAndSts xmlns="">
      <OrgnlMsgId>00012016-02-25000000023</OrgnlMsgId>
      <name>Mr Piet</name>
    </OrgnlGrpInfAndSts>
    <OrgnlGrpInfAndSts xmlns="">
      <OrgnlMsgId>00012016-02-25000000022</OrgnlMsgId>
      <name>Mrs Name</name>
    </OrgnlGrpInfAndSts>
  </FIToFIPmtStsRp>
</Document>

您将使用 CAST(REPLACE(CAST(TheXMLHere AS NVARCHAR(MAX)),' xmlns=""','') AS XML) 去除错误的空命名空间....

You'd use CAST(REPLACE(CAST(TheXMLHere AS NVARCHAR(MAX)),' xmlns=""','') AS XML) to get rid of the wrong empty namespaces....

这是重复命名空间 - 但这在语法上是正确的，但很烦人(阅读此处:https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml 语句)

This is repeating the namespace - but this is syntactically correct, yet annoying (read here: https://connect.microsoft.com/SQLServer/feedback/details/265956/suppress-namespace-attributes-in-nested-select-for-xml-statements)

DECLARE @lim_Live_Inbound TABLE(lim_msg_id VARCHAR(100),lim_request_transaction_id VARCHAR(100),client_name VARCHAR(100));
INSERT INTO @lim_Live_Inbound VALUES
 ('021/00210006/20160225/000002','00012016-02-25000000023','Mr Piet')
,('021/00210006/20160225/000002','00012016-02-25000000022','Mrs Name');

WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04')
,GrpMsg AS
(
    SELECT DISTINCT lim_msg_id AS MsgId
    FROM @lim_Live_Inbound
)
SELECT MsgId AS [GrpHdr/MsgId]
     ,(
        SELECT innerTbl.lim_request_transaction_id AS OrgnlMsgId
              ,innerTbl.client_name AS name
        FROM @lim_Live_Inbound AS innerTbl
        WHERE innerTbl.lim_msg_id=GrpMsg.MsgId
        FOR XML PATH('OrgnlGrpInfAndSts'),TYPE
      ) 
FROM GrpMsg
FOR XML PATH('FIToFIPmtStsRp'),ROOT('Document')

结果

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
  <FIToFIPmtStsRp>
    <GrpHdr>
      <MsgId>021/00210006/20160225/000002</MsgId>
    </GrpHdr>
    <OrgnlGrpInfAndSts xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
      <OrgnlMsgId>00012016-02-25000000023</OrgnlMsgId>
      <name>Mr Piet</name>
    </OrgnlGrpInfAndSts>
    <OrgnlGrpInfAndSts xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
      <OrgnlMsgId>00012016-02-25000000022</OrgnlMsgId>
      <name>Mrs Name</name>
    </OrgnlGrpInfAndSts>
  </FIToFIPmtStsRp>
</Document>

这是第一种方法

我不知道您的数据来自哪里，但是 - 绝对是硬编码 - 这就是方法:

This is the first approach

I don't know where your data comes from, but - absolutely hard coded - this was the approach:

WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04')
SELECT 0 AS [GrpHdr/MsgId]
      ,0 AS [OrgnlGrpInfAndSts/OrgnlMsgId]
FOR XML PATH('FIToFIPmtStsRp'),ROOT('Document')

结果

<Document xmlns="urn:iso:std:iso:20022:tech:xsd:001.002.001.04">
  <FIToFIPmtStsRp>
    <GrpHdr>
      <MsgId>0</MsgId>
    </GrpHdr>
    <OrgnlGrpInfAndSts>
      <OrgnlMsgId>0</OrgnlMsgId>
    </OrgnlGrpInfAndSts>
  </FIToFIPmtStsRp>
</Document>

这篇关于SQL Server 子节点中的空白 xml 命名空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！