Creating XML in SQL using namespaces

  • XMLuser007

    Grasshopper

    Points: 12

    Hello everyone,

    Please help!

    I have a sql statement that creates the following 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 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>123456789</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>

    The namespaces are repeated in each parent element and I need that to only show once at the root so the output is 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:ID>123456789</ram:ID>

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

    This is a small portion of my sql, I have several subqueries, how can I acomplish this within my sql? Thanks!

    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

    ssarrechnung.intnr AS 'ram:ID',

    'INVOICE' as 'ram:Name',

    '380' as 'ram:TypeCode',

    (

    select

    (Select

    102 as "@format", convert(varchar(10), ssarrechnung.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

    (

    123456789

    )

    FOR xml path(''), elements, root('rsm:CrossIndustryDocument')

     

     

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply