Blank xml namespace in child nodes in SQL Server

Question

Hi i need help with the following in sql:

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>

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

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>

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

must generate like this

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

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.

I suppose this is SQL Server, please tag with the right RDBMS (vendor and version) ... And please describe where your data is coming from. And very important: Is there always exactly one element in GrpHdr and in OrgnlGrpInfAndSts or might this be 1:n nested? — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 26, 2016 at 10:03
hi there can be multiple GrpHdr or OrgnlGrpInfAndSts. My big problem is the empty xlmns becuse when i use the same xml generated with the query and i read it ;with xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:001.002.001.04') select @xml.value('(Document/FIToFIPmtStsRpt/GrpHdr/MsgId/node())[1]', 'varchar(100)') it now brings back nothing even if there is a value in — dragonfly
– dragonfly, Commented Feb 26, 2016 at 10:17
You should not create your XML with string operations. SELECT ... FOR XML PATH offers great support to do this natively. Please edit your question and add a simplified example of your data sources. Add at least 2 rows to data which might be 1:n. — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 26, 2016 at 10:19

Thom A · Accepted Answer · 2024-07-08 14:52:15Z

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

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')

The result

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

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

An alternative approach to fit your sample data

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')

The result

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

Otherwise, you could try the following. 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')

The result

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

How do i get multiple OrgnlGrpInfAndSts nodes under each other with out repeating the FIToFIPmtStsRp?
@dragonfly, If you follow my advise to add some simplified sample data I'll show you how to do this. The key is select ... for xml path(''), TYPE .
The way you get rid of the incorrect xmlns="" is a hack and not generalizable—for example, if you had that inside a text node like <x>blah blah xmlns="" blah blah</x>. Unfortunately, the version with crazy repeated namespaces is safer/less wrong.

Collectives™ on Stack Overflow

Blank xml namespace in child nodes in SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related