Creating XML in SQL using namespaces

• XMLuser007

  SSC Enthusiast

  Points: 112

  More actions

  August 28, 2019 at 7:32 am

  #3674966

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

  More actions

  August 29, 2019 at 8:10 am

  #3675330

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

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