Using SQL Server 2008 to create an XML file is missing header and footer

Question

I'm using SQL Server 2008 to create an XML file based on a given structure. The query I'm using right now is below:

select 'ABC123' as SourceTradingPartner,
     'ABC06EMP' as DestinationTradingPartner,
     right('                    ' + 'E_' + cast(WorkOrderHeader.EmpNumber as varchar), 20) as WorkOrder,
     WorkOrderHeader.Name as WorkOrderDescription,
     'OPR' as ResponsiblePersonID,
     'MEXICO' as DivisionID,
     'A' as SynchIndicatorID
    , (
    select '0001' as WorkOrderLineReference,
             'CONSIGN' as Item,
             '2018-03-09' as RequiredManufacturingStartDate,
             '2018-03-09' as OpenDate,
             '2018-03-09' as DueDate,
             '1' as OrderQty,
             'W' as RoutingUsedFlag,
             'I' as BillOfMaterialUsedFlag,
             'B' as ScheduleID,
             '3' as QAStatusID,
             'CNSG' as AccountingGroupCode,
             'R' as StateCode,
             '134800' as DueTime,
             '144846' as OpenTime,
             '134800' as RequiredManufacturingStartTime,
             '2' as InventoryStatus,
             'A' as SynchIndicatorID,
             'Y' as SubstitutePriorityMethod
        , (
        select 'A' as SynchIndicatorID,
                 '10' as Increment
            ,(
            select 'A' as SynchIndicatorID,
                     '  10' as Operation,
                     'CONSIGN' as [Function],
                     'I' AS OperationType,
                     rtrim(isnull(WorkOrderRouting.WorkCenter, '')) as WorkCenter
                , (
                select 'A' as SynchIndicatorID,
                         right('    ' + cast(row_number() over (partition by WorkOrderRoutingTool.Employee
                                                                             order by WorkOrderRoutingTool.id desc, WorkOrderRoutingTool.PartNumber)
                                                    as varchar), 4) as ToolSeq,
                         WorkOrderRoutingTool.PartNumber as ToolID,
                         cast(WorkOrderRoutingTool.Qty as varchar) as ToolQuantity
                from ZCONSIGN WorkOrderRoutingTool with (nolock)
                where WorkOrderRoutingTool.Employee = WorkOrderHeader.OID
                for xml auto, elements, type
                )
            from Employees WorkOrderRouting with (nolock)
            where WorkOrderRouting.OID = WorkOrderHeader.OID
            for xml auto, elements, type
            )
            ,(
            select 'A' as SynchIndicatorID
            from Employees WorkOrderRoutingAddendum with (nolock)
            where WorkOrderRoutingAddendum.OID = WorkOrderHeader.OID
            for xml auto, elements, type
            )
        from Employees WorkOrderRoutingHeader with (nolock)
        where WorkOrderRoutingHeader.OID = WorkOrderHeader.OID
        for xml auto, elements, type
        )
    from Employees WorkOrderLine with (nolock)
    where WorkOrderLine.OID = WorkOrderHeader.OID
    for xml auto, elements, type
    )
    ,(
    select 'A' as SynchIndicatorID,
             '4' as PlanningStatus,
             '2' as ManufacturingStatus
    from Employees WorkOrderLineAddendum with (nolock)
    where WorkOrderLineAddendum.OID = WorkOrderHeader.OID
    for xml auto, elements, type
    )
from Employees WorkOrderHeader with (nolock)
where WorkOrderHeader.EmpNumber = 10171
order by WorkOrderHeader.EmpNumber
for xml auto, elements, type

When the query is executed, it gives me the result shown below:

<WorkOrderHeader>
  <SourceTradingPartner>ABC999</SourceTradingPartner>
  <DestinationTradingPartner>ABC06EMP</DestinationTradingPartner>
  <WorkOrder>             E_10171</WorkOrder>
  <WorkOrderDescription>JOHNNY WALTERS</WorkOrderDescription>
  <ResponsiblePersonID>OPR</ResponsiblePersonID>
  <DivisionID>USA</DivisionID>
  <SynchIndicatorID>A</SynchIndicatorID>
  <WorkOrderLine>
    <WorkOrderLineReference>0001</WorkOrderLineReference>
    <Item>CONSIGN</Item>
    <RequiredManufacturingStartDate>2018-03-09</RequiredManufacturingStartDate>
    <OpenDate>2018-03-09</OpenDate>
    <DueDate>2018-03-09</DueDate>
    <OrderQty>1</OrderQty>
    <RoutingUsedFlag>W</RoutingUsedFlag>
    <BillOfMaterialUsedFlag>I</BillOfMaterialUsedFlag>
    <ScheduleID>B</ScheduleID>
    <QAStatusID>3</QAStatusID>
    <AccountingGroupCode>CNSG</AccountingGroupCode>
    <StateCode>R</StateCode>
    <DueTime>134800</DueTime>
    <OpenTime>144846</OpenTime>
    <RequiredManufacturingStartTime>134800</RequiredManufacturingStartTime>
    <InventoryStatus>2</InventoryStatus>
    <SynchIndicatorID>A</SynchIndicatorID>
    <SubstitutePriorityMethod>Y</SubstitutePriorityMethod>
    <WorkOrderRoutingHeader>
      <SynchIndicatorID>A</SynchIndicatorID>
      <Increment>10</Increment>
      <WorkOrderRouting>
        <SynchIndicatorID>A</SynchIndicatorID>
        <Operation>  10</Operation>
        <Function>CONSIGNA</Function>
        <OperationType>I</OperationType>
        <WorkCenter>1642</WorkCenter>
        <WorkOrderRoutingTool>
          <SynchIndicatorID>A</SynchIndicatorID>
          <ToolSeq>   1</ToolSeq>
          <ToolID>HT9001-003</ToolID>
          <ToolQuantity>19.00</ToolQuantity>
        </WorkOrderRoutingTool>
      </WorkOrderRouting>
      <WorkOrderRoutingAddendum>
        <SynchIndicatorID>A</SynchIndicatorID>
      </WorkOrderRoutingAddendum>
    </WorkOrderRoutingHeader>
  </WorkOrderLine>
  <WorkOrderLineAddendum>
    <SynchIndicatorID>A</SynchIndicatorID>
    <PlanningStatus>4</PlanningStatus>
    <ManufacturingStatus>2</ManufacturingStatus>
  </WorkOrderLineAddendum>
</WorkOrderHeader>

The result looks fine, and it's almost complete. But it's missing the 'header' and 'footer' information (what i call 'footer' is just the closing tag for the 'header'). The header I need to have looks like this:

<?xml version="1.0" encoding="utf-8"?>
<Mapi destination_reference="ABC06EMP" name="WorkOrder" source_reference="ABC999" xmlns="http://www.company.com/mapi/WorkOrder" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

And the footer is just this:

</Mapi>

I've tried to use the WITH XMLNAMESPACES approach, but I get the namespace declaration repeated in some of the child nodes (I think this is a known issue). I'm not sure how to solve this.

Can anyone help?

Thanks in advance.

Answer 1

You can try the following query statement.

DECLARE @FinalXML NVARCHAR(MAX)=
    (
        SELECT '<?xml version="1.0" encoding="UTF-8"?>'
           +
           CAST((select 'ABC123' as SourceTradingPartner,
            'ABC06EMP' as DestinationTradingPartner,
            'OPR' as ResponsiblePersonID
        , (select '0001' as WorkOrderLineReference,
                    'CONSIGN' as Item,
                    '2018-03-09' as RequiredManufacturingStartDate,
                    '2018-03-09' as OpenDate
            for xml path('WorkOrderRoutingHeader'), elements, type
            )
        ,(select 'A' as SynchIndicatorID,
                    '4' as PlanningStatus
            for xml path('WorkOrderLine'), elements, type
            )
    for xml path('Mapi'), ELEMENTS XSINIL) AS NVARCHAR(MAX))
    );

    SELECT @FinalXML;

If you will face any problem, feel free to write in comment section. Thanks.

Answer 2

As you've found out you cannot out-trick the namespace-issue. Yes, it is a well known issue (the related connect issue has disappaered - after 10 years! - in the meanwhile...)

The approach in one of the answers leads to xmlns="", which is not just a tiny oops, doesn't matter. Alle elements below are not living in the same default namespace!

You'll either have to accept the repeated namespaces (not wrong, but bloating your output and very annoying) or you have to accept more or less ugly workarounds on string base.

In your case I'd suggest to cast the XML to NVARCHAR(MAX) and use simple string concatenation to add prolog and root:

...=N'<?xml ...?><mapi ...> + YourXmlAsString + N'</mapi>';

And you must be aware, that the declaration is not just some silly additive. If you write an encoding of utf-8, your file on disc should be utf-8-encoded actually. Otherwise it would be like a text, where you read on the first page "written in English", but the rest of the book is written in French entirely.