none
Creating XML with Namespaces and displaying only on root element RRS feed

  • Question

  • Hello,

    I have a sql statement that outputs the following XML

    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <rsm:SpecifiedExchangedDocumentContext xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <ram:GuidelineSpecifiedDocumentContextParameter xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
        </ram:GuidelineSpecifiedDocumentContextParameter>
      </rsm:SpecifiedExchangedDocumentContext>
      <rsm:HeaderExchangedDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <ram:ID>12345678</ram:ID>
        <ram:Name>INVOICE</ram:Name>
        <ram:TypeCode>380</ram:TypeCode>
        <ram:IssueDateTime xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <udt:DateTimeString xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" format="102">20190823</udt:DateTimeString>
        </ram:IssueDateTime>
        <ram:IncludedNote xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
          <ram:Content>Please pay on time</ram:Content>
        </ram:IncludedNote>
      </rsm:HeaderExchangedDocument>
    </rsm:CrossIndustryDocument>

    I would like the namespaces only to show in the root element so the output shows like this

    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <rsm:SpecifiedExchangedDocumentContext>
        <ram:GuidelineSpecifiedDocumentContextParameter>
          <ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
        </ram:GuidelineSpecifiedDocumentContextParameter>
      </rsm:SpecifiedExchangedDocumentContext>
    <rsm:HeaderExchangedDocument>
    <ram:Name>INVOICE</ram:Name>
    <ram:TypeCode>380</ram:TypeCode>
    <ram:IssueDateTime>
    <udt:DateTimeString format="102">20190823</udt:DateTimeString>
    </ram:IssueDateTime>
    <ram:IncludedNote">
    <ram:Content>Please pay on time</ram:Content>
    </ram:IncludedNote>
    </rsm:HeaderExchangedDocument>
    </rsm:CrossIndustryDocument>

    I have several sub-queries in my sql selection, this is just a small part of it, can i make an adjustment to my sql statement to get the desired output? Thanks for your help!

    with XMLNAMESPACES (
    'http://www.w3.org/2001/XMLSchema-instance' as xsi,
    'urn:ferd:CrossIndustryDocument:invoice:1p0' as rsm,
    'urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12' as ram,
    'urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15' as udt)

    Select 
    (SELECT
    (select
    'urn:ferd:CrossIndustryDocument:invoice:1p0:comfort' as 'ram:ID'
    FOR xml path('ram:GuidelineSpecifiedDocumentContextParameter'), TYPE)
    FOR xml path('rsm:SpecifiedExchangedDocumentContext'), TYPE),
    (SELECT 
     invoice.intnr AS 'ram:ID',
     'INVOICE' as 'ram:Name',
    '380' as 'ram:TypeCode',
    (
    select
    (Select 
    102 as "@format", convert(varchar(10), invoice.datum, 112) 
    for xml path('udt:DateTimeString'),type)
    FOR xml path('ram:IssueDateTime'),TYPE),
    (Select 
    'Please pay on time' as 'ram:Content'
    FOR xml path('ram:IncludedNote'),TYPE)
    FOR xml path('rsm:HeaderExchangedDocument'),TYPE)
    FROM   invoice
    WHERE  invoice.intnr in 
    (
    12345678
    )
    FOR    xml path(''), elements, root('rsm:CrossIndustryDocument')


    • Edited by XMLuser007 Tuesday, August 27, 2019 1:46 PM
    Tuesday, August 27, 2019 1:46 PM

All replies

  • Hi XMLuser007,

    Please provide a real sample that includes DDL and sample data population:

    1. CREATE TABLE ...
    2. INSERT INTO …


    Otherwise, it is impossible to run your SELECT statements producing XML.

    Tuesday, August 27, 2019 1:57 PM
  • Hi XMLuser007,

    Because just crickets chirp, I took the liberty and created a DDL and sample data population based on my imagination.

    Check it out a solution below. It is based on the XQuery FLWOR expression. XQuery is much more natural/native solution to query XML, shape its structure, and handle namespaces.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE(
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, intnr INT
    	, datum DATE
    );
    INSERT INTO @tbl(intnr, datum)
    VALUES
    (12345678, '2019-08-23')
    -- DDL and sample data population, end
    
    SELECT
    (SELECT * 
    FROM @tbl
    FOR XML PATH('row'),TYPE, ROOT('root')).query('
    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15"
                               xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12"
                               xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0"
                               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">{
    for $x in /root/row
       return (<rsm:SpecifiedExchangedDocumentContext>
    		<ram:GuidelineSpecifiedDocumentContextParameter>
    			<ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
    		</ram:GuidelineSpecifiedDocumentContextParameter>
    	</rsm:SpecifiedExchangedDocumentContext>,
    	<rsm:HeaderExchangedDocument>
    		<ram:ID>{data($x/intnr)}</ram:ID>
    		<ram:Name>INVOICE</ram:Name>
    		<ram:TypeCode>380</ram:TypeCode>
    		<ram:IssueDateTime>
    			<udt:DateTimeString format="102">{data($x/datum)}</udt:DateTimeString>
    		</ram:IssueDateTime>
    		<ram:IncludedNote>
    			<ram:Content>Please pay on time</ram:Content>
    		</ram:IncludedNote>
    	</rsm:HeaderExchangedDocument>)
    }</rsm:CrossIndustryDocument>');

    Output:

    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <rsm:SpecifiedExchangedDocumentContext>
        <ram:GuidelineSpecifiedDocumentContextParameter>
          <ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
        </ram:GuidelineSpecifiedDocumentContextParameter>
      </rsm:SpecifiedExchangedDocumentContext>
      <rsm:HeaderExchangedDocument>
        <ram:ID>12345678</ram:ID>
        <ram:Name>INVOICE</ram:Name>
        <ram:TypeCode>380</ram:TypeCode>
        <ram:IssueDateTime>
          <udt:DateTimeString format="102">2019-08-23</udt:DateTimeString>
        </ram:IssueDateTime>
        <ram:IncludedNote>
          <ram:Content>Please pay on time</ram:Content>
        </ram:IncludedNote>
      </rsm:HeaderExchangedDocument>
    </rsm:CrossIndustryDocument>



    Tuesday, August 27, 2019 6:55 PM
  • Thanks so much for taking the time to help me, I have been reading a lot of forums and I just could find any answers, you are great!

    One more thing, how do i avoid the parent and elements to be shown when there is no data at all?

    For example there is no content so i would want the included note and the content tags to not show at all.

    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <rsm:SpecifiedExchangedDocumentContext>
        <ram:GuidelineSpecifiedDocumentContextParameter>
          <ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
        </ram:GuidelineSpecifiedDocumentContextParameter>
      </rsm:SpecifiedExchangedDocumentContext>
      <rsm:HeaderExchangedDocument>
        <ram:ID>12345678</ram:ID>
        <ram:Name>INVOICE</ram:Name>
        <ram:TypeCode>380</ram:TypeCode>
        <ram:IssueDateTime>
          <udt:DateTimeString format="102">2019-08-23</udt:DateTimeString>
        </ram:IssueDateTime>
        <ram:IncludedNote>
    <ram:Content/>
        </ram:IncludedNote>
      </rsm:HeaderExchangedDocument>
    </rsm:CrossIndustryDocument>

    Wednesday, August 28, 2019 1:21 PM
  • Hi XMLuser007,

    I added an additional column: Content VARCHAR(100) NULL to control that XML fragment.

    Additionally, it is possible to use a regular SQL Server variable for the same purpose.

    -- DDL and sample data population, start
    DECLARE @tbl TABLE(
    	ID INT IDENTITY(1,1) PRIMARY KEY
    	, intnr INT
    	, datum DATE
    	, Content VARCHAR(100) NULL
    );
    INSERT INTO @tbl(intnr, datum, Content)
    VALUES
    (12345678, '2019-08-23', NULL)
    -- DDL and sample data population, end
    SELECT * FROM @tbl;
    
    SELECT
    (SELECT * 
    FROM @tbl
    FOR XML PATH('row'),TYPE, ROOT('root')).query('
    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15"
                               xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12"
                               xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0"
                               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    {
    for $x in /root/row
       return (<rsm:SpecifiedExchangedDocumentContext>
    		<ram:GuidelineSpecifiedDocumentContextParameter>
    			<ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
    		</ram:GuidelineSpecifiedDocumentContextParameter>
    	</rsm:SpecifiedExchangedDocumentContext>,
    	<rsm:HeaderExchangedDocument>
    		<ram:ID>{data($x/intnr)}</ram:ID>
    		<ram:Name>INVOICE</ram:Name>
    		<ram:TypeCode>380</ram:TypeCode>
    		<ram:IssueDateTime>
    			<udt:DateTimeString format="102">{data($x/datum)}</udt:DateTimeString>
    		</ram:IssueDateTime>{
    		if ($x/Content) then
    			<ram:IncludedNote>
    				<ram:Content>{data($x/Content)}</ram:Content>
    			</ram:IncludedNote>
    		else
    		   ()}
    	</rsm:HeaderExchangedDocument>)
    }</rsm:CrossIndustryDocument>');

    Output:

    <rsm:CrossIndustryDocument xmlns:udt="urn:un:unece:uncefact:data:standard:UnqualifiedDataType:15" xmlns:ram="urn:un:unece:uncefact:data:standard:ReusableAggregateBusinessInformationEntity:12" xmlns:rsm="urn:ferd:CrossIndustryDocument:invoice:1p0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <rsm:SpecifiedExchangedDocumentContext>
        <ram:GuidelineSpecifiedDocumentContextParameter>
          <ram:ID>urn:ferd:CrossIndustryDocument:invoice:1p0:comfort</ram:ID>
        </ram:GuidelineSpecifiedDocumentContextParameter>
      </rsm:SpecifiedExchangedDocumentContext>
      <rsm:HeaderExchangedDocument>
        <ram:ID>12345678</ram:ID>
        <ram:Name>INVOICE</ram:Name>
        <ram:TypeCode>380</ram:TypeCode>
        <ram:IssueDateTime>
          <udt:DateTimeString format="102">2019-08-23</udt:DateTimeString>
        </ram:IssueDateTime>
      </rsm:HeaderExchangedDocument>
    </rsm:CrossIndustryDocument>


    Wednesday, August 28, 2019 2:11 PM
  • Hi XMLuser007,

    What's the latest on your end?
    Were you able to make a progress with the recommendations and code samples I provided?

    A conditional logic via if/else construct will take care of optional XML fragments.
    Thursday, August 29, 2019 2:50 PM
  • Thank you so much!! it works as expected, I have a few sample files to test to ensure all is covered.

    Another thing to add to this complex query is a reference to a stylesheet at the very top, it this possible?

    Tuesday, September 3, 2019 10:50 AM
  • Hi XMLuser007,

    1. Glad to hear that everything is working for you.
    2. Please open a new thread for the new question regarding the XSLT stylesheet.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Tuesday, September 3, 2019 1:04 PM