Search code examples
sql-serverxmlt-sqlxml-namespaces

Generate XML files with various namespaces using SQL Server

I need to generate XML from SQL Server that the end result looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<kmsg xmlns='http://xml.aaa.net/K809' xmlns:env='http://xml.aaa.net/K809/k8msgEnvelope' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'  xsi:schemaLocation='http://xml.aaa.net/K809 k8Order.xsd'>
    <header>
        <env:envelope>
            <env:source branch='2172' endpoint='' machine='0' password=''/>
            <env:destination branch='2172' endpoint='050107' machine='0'/>
            <env:payload>ORDERRESPONSE</env:payload>
            <env:cfcompany>01</env:cfcompany>
            <env:service>ILDLIVE</env:service>
        </env:envelope>
    </header>
    <body>
        <PurchaseOrder xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>
            <OrderReferences>
                <CrossReference>ABC 1234567</CrossReference>
            </OrderReferences>
        <Extensions xmlns='http://xml.aaa.net/k8msg/k8OrderExtensions'>
                <Direct>FALSE</Direct>
            </Extensions>
            <Supplier>
                <SupplierReferences>
                    <BuyersCodeForSupplier>050107</BuyersCodeForSupplier>
                </SupplierReferences>
            </Supplier>
            <Delivery>
                <PreferredDate>2022-12-24</PreferredDate>
                <SpecialInstructions>Please contact store buyer to confirm quantities and prices, all orders and prices will be final after acceptance of order</SpecialInstructions>
            </Delivery>
            <OrderLine Action='Add' TypeCode='New' TypeDescription='New Item'>
                <Product>
                    <SuppliersProductCode>1000486</SuppliersProductCode>
                    <BuyersProductCode>1000486</BuyersProductCode>
                </Product>
                <Quantity UOMCode='EA' UOMDescription='Each'>
                    <Amount>10</Amount>
                </Quantity>
                <Delivery>
                    <PreferredDate>2022-12-24</PreferredDate>
                </Delivery>
            </OrderLine>
        </PurchaseOrder>
    </body>
</kmsg>

The basic layout has been done but I'm facing trouble with 2 items:

  1. The namespaces at the top. I'm generating the namespaces but it generates out of sync in order of creation and I'm also getting &quot characters. I suspect a characterset mismatch or something similar. How do I get the namespaces to view in the exact order of the document?
  2. How do I add namespaces in the middle of the document? The PurchaseOrder and Extentions tags both have namespaces within them. Can these also be declared at the top of the query, or do I need to add them as static values in SQL?

Here is the code I have currently:

WITH XMLNAMESPACES (
'xsi="http://xml.aaa.net/K809 k8Order.xsd"' AS schemaLocation,
'xsi="http://www.w3.org/2001/XMLSchema-instance"' AS xsi,
'env="http://xml.aaa.net/K809/k8msgEnvelope"' AS env,
'xmlns="http://xml.aaa.net/K809"' AS xmlns1)

SELECT TOP 1
''                                      AS 'header/env:envelope/env:source/@password',
''                                      AS 'header/env:envelope/env:source/@machine',
A.SUPPLIER                              AS 'header/env:envelope/env:source/@Endpoint',
B.SRCLOC                                AS 'header/env:envelope/env:source/@Branch',
'ORDERRESPONSE'                         AS 'header/env:envelope/env:payload',
--B.SRCLOC                              AS DestinationBranch,
C.FREEATTR3                             AS 'header/env:envelope/env:cfCompany',
'ILDLive'                               AS 'header/env:envelope/env:service',
'urn:schemas-basda-org:2000:purchaseOrder:xdr:3>' AS 'Body/@PurchaseOrder',
A.SUPPLIER                              AS 'Body/PurchaseOrder/Supplier/SupplierReferences/BuyersCodeForSupplier',
DATEDIFF(DAY,'1989/12/31',A.EXPDATEPRD) AS 'Body/PurchaseOrder/Delivery/PreferredDate', --CONVERT: 2022-12-24
'Test text for Special instructions'    AS 'Body/PurchaseOrder/Delivery/SpecialInstructions',
'New item'                              AS 'Body/PurchaseOrder/OrderLine/@TypeDescription',
'New'                                   AS 'Body/PurchaseOrder/OrderLine/@TypeCode',
'Add'                                   AS 'Body/PurchaseOrder/OrderLine/@Action',
A.ITEM                                  AS 'Body/PurchaseOrder/OrderLine/Product/BuyersProductCode',
D.ExternalItemMasterID                  AS 'Body/PurchaseOrder/OrderLine/Product/SuppliersProductCode',
F.UnitOfMeasureDesc                     AS 'Body/PurchaseOrder/OrderLine/Quantity/@UOMDescription',
E.VolumetricValue                       AS 'Body/PurchaseOrder/OrderLine/Quantity/@UOMCode',
CAST(SUM(A.QEDIT) AS INT)               AS 'Body/PurchaseOrder/OrderLine/Quantity/Amount'
--INTO #XMLTemp
FROM TableMain  AS A
    JOIN Table1         AS B ON A.DESTWHS = B.SRCWHS
    JOIN Table2         AS C ON B.SRCLOC = C.LOCATION
    JOIN Table3         AS D ON A.ITEM = D.ItemCode
    JOIN Table4         AS E ON A.ITEM = E.ItemCode
    JOIN Table5         AS F ON F.ItemCode=A.ITEM
WHERE E.Measurement = 'Unit of Buy'
--AND DATEDIFF(DAY,'1989/12/31',A.PRUNDATPRD) = GETDATE()
--ONLY THE LATEST PROPOSALS
GROUP BY B.SRCLOC,C.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,D.ExternalItemMasterID,A.ITEM,E.VolumetricValue,F.UnitOfMeasureDesc 
FOR XML PATH('kmsg')

and here is the output I have:

<kmsg xmlns:xmlns1="xmlns=&quot;http://xml.aaa.net/K809&quot;" xmlns:env="env=&quot;http://xml.aaa.net/K809/k8msgEnvelope&quot;" xmlns:xsi="xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;" xmlns:schemaLocation="xsi=&quot;http://xml.aaa.net/K809 k8Order.xsd&quot;">
  <header>
    <env:envelope>
      <env:source password="" machine="" Endpoint="050354" Branch="1012" />
      <env:payload>ORDERRESPONSE</env:payload>
      <env:cfCompany>01</env:cfCompany>
      <env:service>ILDLive</env:service>
    </env:envelope>
  </header>
  <Body PurchaseOrder="urn:schemas-basda-org:2000:purchaseOrder:xdr:3&gt;">
    <PurchaseOrder>
      <Supplier>
        <SupplierReferences>
          <BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
        </SupplierReferences>
      </Supplier>
      <Delivery>
        <PreferredDate>-20744</PreferredDate>
        <SpecialInstructions>Test text for Special instructions</SpecialInstructions>
      </Delivery>
      <OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
        <Product>
          <BuyersProductCode>1034623</BuyersProductCode>
          <SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
        </Product>
        <Quantity UOMDescription="Each" UOMCode="EA">
          <Amount>2904</Amount>
        </Quantity>
      </OrderLine>
    </PurchaseOrder>
  </Body>
</kmsg>

Any help would be appreciated. I'm not used to working with XML in SQL.

I tried using static namespaces in the SQL part but I don't think that is the way to do it

Solution

  • Given some of the errors there are several things you need to keep in mind with XML documents:

    • namespace URIs are case sensitive.
    • element names are case sensitive, so <Body> is a different element than <body>.
    • attribute names case sensitive, so <source Endpoint="..."/> is a different attribute than <source endpoint="..."/>.

    WITH XMLNAMESPACES has a particular syntax where 'urn' as prefix defines a namespace URN with a prefix and default 'urn' defines a namespace URN to be used without a prefix, aka. the default namespace.

    The following SQL probably isn't correct for your environment but produces XML similar to the output you are looking for - if you update your question to include sample DDL and data this could be tweaked somewhat:

    --
-- Setup data...
--
select 'WHS' as DESTWHS, cast('2022-12-24' as date) as EXPDATEPRD, '1034623' as ITEM, 10 as QEDIT, '050354' as SUPPLIER
into dbo.TableMain; --A

select '2172' as SRCLOC, 'WHS' as SRCWHS
into dbo.Table1; --B

select '01' as FREEATTR3, '2172' as [LOCATION]
into dbo.Table2; --C

select 'KHN4.200CX' as ExternalItemMasterID, '1034623' as ItemCode
into dbo.Table3; --D

select '1034623' as ItemCode, 'Unit of Buy' as Measurement, 'EA' as VolumetricValue
into dbo.Table4; --E

select '1034623' as ItemCode, 'Each' as UnitOfMeasureDesc
into dbo.Table5; --F

--
-- XML query...
--
WITH XMLNAMESPACES (
  'urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01' as po,
  'http://xml.aaa.net/K809/k8msgEnvelope' AS env,
  'http://www.w3.org/2001/XMLSchema-instance' as xsi,
  default 'http://xml.aaa.net/K809'
)
SELECT TOP 1
  'http://xml.aaa.net/K809 k8Order.xsd' as [@xsi:schemaLocation],
  B.SRCLOC                                AS [header/env:envelope/env:source/@branch],
  A.SUPPLIER                              AS [header/env:envelope/env:source/@endpoint],
  ''                                      AS [header/env:envelope/env:source/@machine],
  ''                                      AS [header/env:envelope/env:source/@password],
  'ORDERRESPONSE'                         AS [header/env:envelope/env:payload],
  C.FREEATTR3                             AS [header/env:envelope/env:cfcompany],
  'ILDLive'                               AS [header/env:envelope/env:service],
  (
    select
      cast('<Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions"><Direct>FALSE</Direct></Extensions>' as xml).query('/*'),
      A.SUPPLIER                           AS [po:Supplier/po:SupplierReferences/po:BuyersCodeForSupplier],
      A.EXPDATEPRD                         AS [po:Delivery/po:PreferredDate],
      'Test text for Special instructions' AS [po:Delivery/po:SpecialInstructions],
      'New item'                           AS [po:OrderLine/@TypeDescription],
      'New'                                AS [po:OrderLine/@TypeCode],
      'Add'                                AS [po:OrderLine/@Action],
      A.ITEM                               AS [po:OrderLine/po:Product/po:BuyersProductCode],
      D.ExternalItemMasterID               AS [po:OrderLine/po:Product/po:SuppliersProductCode],
      F.UnitOfMeasureDesc                  AS [po:OrderLine/po:Quantity/@UOMDescription],
      E.VolumetricValue                    AS [po:OrderLine/po:Quantity/@UOMCode],
      CAST(SUM(A.QEDIT) AS INT)            AS [po:OrderLine/po:Quantity/po:Amount]
    for xml path('po:PurchaseOrder'), type
  ).query('
  declare default element namespace "urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01";
  <PurchaseOrder> { /po:PurchaseOrder/* } </PurchaseOrder>') as [body]
FROM TableMain  AS A
    JOIN Table1         AS B ON A.DESTWHS = B.SRCWHS
    JOIN Table2         AS C ON B.SRCLOC = C.LOCATION
    JOIN Table3         AS D ON A.ITEM = D.ItemCode
    JOIN Table4         AS E ON A.ITEM = E.ItemCode
    JOIN Table5         AS F ON F.ItemCode=A.ITEM
WHERE E.Measurement = 'Unit of Buy'
GROUP BY B.SRCLOC,C.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,D.ExternalItemMasterID,A.ITEM,E.VolumetricValue,F.UnitOfMeasureDesc 
FOR XML PATH('kmsg')

    Which yields the XML output:

    <kmsg xmlns="http://xml.aaa.net/K809"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:env="http://xml.aaa.net/K809/k8msgEnvelope"
    xmlns:po="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01" xsi:schemaLocation="http://xml.aaa.net/K809 k8Order.xsd">
    <header>
        <env:envelope>
            <env:source password="" machine="" endpoint="050354" branch="2172"/>
            <env:payload>ORDERRESPONSE</env:payload>
            <env:cfcompany>01</env:cfcompany>
            <env:service>ILDLive</env:service>
        </env:envelope>
    </header>
    <body>
        <PurchaseOrder xmlns="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01">
            <Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions">
                <Direct>FALSE</Direct>
            </Extensions>
            <Supplier>
                <SupplierReferences>
                    <BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
                </SupplierReferences>
            </Supplier>
            <Delivery>
                <PreferredDate>2022-12-24</PreferredDate>
                <SpecialInstructions>Test text for Special instructions</SpecialInstructions>
            </Delivery>
            <OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
                <Product>
                    <BuyersProductCode>1034623</BuyersProductCode>
                    <SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
                </Product>
                <Quantity UOMDescription="Each" UOMCode="EA">
                    <Amount>10</Amount>
                </Quantity>
            </OrderLine>
        </PurchaseOrder>
    </body>
</kmsg>