Creating an XML file containing Header, Detail, and Trailer sections with provided XSD file

Question

I have a requirement to produce an XML file that contains a header, detail, and trailer section. The source data for this XML file will be extracted from a database so I will be using TSQL. The data specifications provide a sample XSD file as well as a sample XML file. The XML file I have created does not match the sample XML file. The tags aren't quite the same. How would I go about replicating the sample XML file? I am feeling like I need to incorporate the sample XSD file some how but I don't have much experience working with XML to know for sure. So far I have something like this:

DECLARE @tmpHeader TABLE
([Header Code] varchar(15) NULL,
[Preferred Provider List Creation Date] datetime NULL,
[ACO Program Code] int NULL)

INSERT INTO @tmpHeader
([Header Code],[Preferred Provider List Creation Date],[ACO Program Code])
VALUES
('HDR_PFPRVDR',CONVERT(date,GETDATE()),'21')

DECLARE @tmpTrailer TABLE
([Trailer Code] varchar(15) NULL,
[Preferred Provider List File Creation Date] datetime NULL,
[Detail Record Count] int NULL)

INSERT INTO @tmpTrailer
([Trailer Code],[Preferred Provider List File Creation Date],[Detail Record Count])
SELECT 
'TRL_PFPRVDR',CONVERT(date,GETDATE()),(select count(*) from (
SELECT distinct
     [ACO Identifier] = 'V130'
    ,[ACO Preferred Provider TIN] = case when TIN.VendorTaxID is NULL then VEN.VendorTaxID else TIN.VendorTaxID end
    ,[Old ACO Preferred Provider TIN] = TIN.Old_TaxID
    ,[ACO Organization Preferred Provider NPI] = NULL
    ,[ACO Individual Preferred Provider NPI] = PRV.NPINumber
    ,[ACO Preferred Provider Shared_Savings Program Effective Date] = CDA.EffDate
    ,[ACO Preferred Provider Shared Savings Program Termination Date] = nullif(CDA.TermDate,'')
FROM Provider PRV (readuncommitted)
    LEFT JOIN Vendor VEN (readuncommitted) ON 
        PRV.Vendor_UniqueID = VEN.Vendor_UniqueID
        and
        VEN.ods_row_current = PRV.ods_row_current
    LEFT JOIN TIN (readuncommitted) ON
        TIN.Vendor_UniqueID = PRV.Vendor_UniqueID
    JOIN CDA (readuncommitted) ON
        CDA.LicenseID = TIN.VendorShortName and CDA.TaxID = TIN.VendorTaxID
    WHERE 
        PRV.ods_row_current = 1
        ) as A)

DECLARE @TempExportTable TABLE
(
  Header XML,
  Detail XML,
  Trailer XML
)


INSERT INTO @TempExportTable VALUES
(
(SELECT [Header Code],[Preferred Provider List Creation Date],[ACO Program Code] FROM @tmpHeader FOR XML AUTO, ELEMENTS),
(SELECT distinct
     [ACO Identifier] = 'V130'
    ,[ACO Preferred Provider TIN] = case when TIN.VendorTaxID is NULL then VEN.VendorTaxID else TIN.VendorTaxID end
    ,[Old ACO Preferred Provider TIN] = TIN.Old_TaxID
    ,[ACO Organization Preferred Provider NPI] = NULL
    ,[ACO Individual Preferred Provider NPI] = PRV.NPINumber
    ,[ACO Preferred Provider Shared_Savings Program Effective Date] = CDA.EffDate
    ,[ACO Preferred Provider Shared Savings Program Termination Date] = nullif(CDA.TermDate,'')
FROM PROVIDER PRV (readuncommitted)
    LEFT JOIN VENDOR (readuncommitted) ON 
        PRV.Vendor_UniqueID = VEN.Vendor_UniqueID
        and
        VEN.ods_row_current = PRV.ods_row_current
    LEFT JOIN TIN (readuncommitted) ON
        TIN.Vendor_UniqueID = PRV.Vendor_UniqueID
    JOIN CDA (readuncommitted) ON
        CDA.LicenseID = TIN.VendorShortName and CDA.TaxID = TIN.VendorTaxID
    WHERE 
        PRV.ods_row_current = 1
        FOR XML AUTO, ELEMENTS),
(SELECT [Trailer Code],[Preferred Provider List File Creation Date],[Detail Record Count] FROM @tmpTrailer FOR XML AUTO, ELEMENTS)
)
SELECT 
   Header as '*',
   Detail as '*',
   Trailer as '*' 
from @TempExportTable 
FOR XML PATH('ExportList')

But I need to produce something more similar to this provided sample XML file:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<ACOParticipantData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Header>
    <HeaderCode>HDR_PFPRVDR</HeaderCode>
    <FileCreationDate>20160101</FileCreationDate>
    <ACOProgCode>21</ACOProgCode>
  </Header>
  <Participants>
    <Participant>
      <ACO_ID>V199</ACO_ID>
      <TIN>123456789</TIN>
      <Old_TIN>987654321</Old_TIN>
      <Org_NPI>1234567890</Org_NPI>
      <Ind_NPI>1234567890</Ind_NPI>
      <CCN>123456</CCN>
      <PRG_Eff_Dt>20160101</PRG_Eff_Dt>
      <PRG_Term_Dt>20161231</PRG_Term_Dt>
    </Participant>
  </Participants>
  <Trailer>
    <TrailerCode>TRL_PFPRVDR</TrailerCode>
    <FileCreationDate>20160101</FileCreationDate>
    <RecordCount>1</RecordCount>
  </Trailer>
</ACOParticipantData>Sample

Here is the sample XSD file:

<?xml version="1.0" encoding="UTF-8"?>           
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="qualified">
<xsd:element name="Header" type="HeaderType"/>
        <xsd:element name="Participants" type="ParticipantsType"/>
        <xsd:element name="Participant" type="ParticipantType"/>
        <xsd:element name="Trailer" type="TrailerType"/>

        <xsd:element name="ACOParticipantData">                
    <xsd:complexType>
                                <xsd:sequence>
            <xsd:element ref="Header"/>
                                            <xsd:element ref="Participants" minOccurs="0" maxOccurs="1"/>
                                            <xsd:element ref="Trailer"/>
                                </xsd:sequence>
                    </xsd:complexType>
        </xsd:element>

        <xsd:complexType name="HeaderType">
                    <xsd:sequence>
                                <xsd:element name="HeaderCode" type="HeaderCodeENUM"/>
                                <xsd:element name="FileCreationDate">
            <xsd:simpleType>
                                                        <xsd:restriction base="xsd:string"/>
                                            </xsd:simpleType>
                                </xsd:element>
                                <xsd:element name="ACOProgCode" type="ACOProgCodeType"/>                                    
                    </xsd:sequence>
        </xsd:complexType>

        <xsd:complexType name="ParticipantsType">
                    <xsd:sequence>
                                <xsd:element ref="Participant" maxOccurs="unbounded"/>
                    </xsd:sequence>
        </xsd:complexType>

        <xsd:complexType name="ParticipantType">                   
    <xsd:sequence>  
        <xsd:element name="ACO_ID" type="OrgType"/>
        <xsd:element name="TIN" type="xsd:string"/>
        <xsd:element name="Old_TIN" nillable="true" type="xsd:string"/>
        <xsd:element name="Org_NPI" nillable="true" type="xsd:string"/>
        <xsd:element name="Ind_NPI" nillable="true" type="xsd:string"/>
        <xsd:element name="CCN" nillable="true" type="xsd:string"/>
        <xsd:element name="PRG_Eff_Dt" type="DateType"/>
        <xsd:element name="PRG_Term_Dt" nillable="true" type="xsd:string"/>
    </xsd:sequence>
        </xsd:complexType>

        <xsd:complexType name="TrailerType">
    <xsd:sequence>
                                <xsd:element name="TrailerCode" type="TrailerCodeENUM"/>
                                <xsd:element name="FileCreationDate">                                     
                                            <xsd:simpleType>
                                                        <xsd:restriction base="xsd:string"/>
                                            </xsd:simpleType>
                                </xsd:element>                         
                                <xsd:element name="RecordCount">
                                            <xsd:simpleType>
                                                        <xsd:restriction base="xsd:integer">
                                                                    <xsd:minInclusive value="0"/>
                                                                    <xsd:maxInclusive value="9999999"/>
                                                        </xsd:restriction>
                                            </xsd:simpleType>
                                </xsd:element>                         
                    </xsd:sequence>
        </xsd:complexType>

        <xsd:simpleType name="HeaderCodeENUM">
                    <xsd:restriction base="xsd:string">
                       <xsd:pattern value="HDR_PFPRVDR"/>
                    </xsd:restriction>
        </xsd:simpleType>

        <xsd:simpleType name="ACOProgCodeType">
                    <xsd:restriction base="xsd:string">
                       <xsd:pattern value="21"/>
                    </xsd:restriction>
        </xsd:simpleType>

        <xsd:simpleType name="TrailerCodeENUM">
                    <xsd:restriction base="xsd:string">
                                <xsd:pattern value="TRL_PFPRVDR"/>
                    </xsd:restriction>
        </xsd:simpleType>

        <xsd:simpleType name="DateType">
                    <xsd:restriction base="xsd:string">
                                <xsd:pattern value="\d{8}"/>
                    </xsd:restriction>
        </xsd:simpleType>

        <xsd:simpleType name="OrgType">
                    <xsd:restriction base="xsd:string">
                                <xsd:pattern value="V\d{3}"/>
                    </xsd:restriction>
        </xsd:simpleType>

</xsd:schema>

Any feedback would be greatly appreciated, thank you!

Shnugo · Accepted Answer · 2016-05-11T07:36:14.130

Without your real table's structure and sample data it is like reading the magic glass bulb, but this should be close:

I created another temp-table to mock your details data

You might avoid the declared XML-variables, but in this case you'd get the declaration of "xsi" in each sub-node repeatedly. No error, but annoying...

Maybe you do not even need the "xsi"-namespace...

DECLARE @tmpHeader TABLE
([Header Code] varchar(15) NULL,
[Preferred Provider List Creation Date] datetime NULL,
[ACO Program Code] int NULL);

INSERT INTO @tmpHeader
([Header Code],[Preferred Provider List Creation Date],[ACO Program Code])
VALUES
('HDR_PFPRVDR',CONVERT(date,GETDATE()),'21');

DECLARE @tmpTrailer TABLE
([Trailer Code] varchar(15) NULL,
[Preferred Provider List File Creation Date] datetime NULL,
[Detail Record Count] int NULL);

INSERT INTO @tmpTrailer
([Trailer Code],[Preferred Provider List File Creation Date],[Detail Record Count])
VALUES ('TRL_PFPRVDR',CONVERT(date,GETDATE()),100); --Replaced your `SELECT COUNT(*) FROM ...` with a fix value

DECLARE @tmpDetail TABLE(ACO_ID VARCHAR(100),TIN BIGINT,Old_TIN BIGINT,Org_NPI BIGINT,Ind_NPI BIGINT,CCN INT,PRG_Eff_Dt DATE,PRG_Term_Dt DATE);
INSERT INTO @tmpDetail VALUES('V199',123456789,987654321,1234567890,1234567890,123456,'20160101','20161231');

--To avoid repeated namespace declarations:
DECLARE @Hd XML=
      ( 
        SELECT h.[Header Code] AS HeaderCode
              ,h.[Preferred Provider List Creation Date] AS FileCreationDate
              ,h.[ACO Program Code] AS ACOProgCode
        FROM @tmpHeader AS h      
        FOR XML PATH('Header'),TYPE
      );
DECLARE @Dt XML=
      (
        SELECT d.ACO_ID
              ,d.TIN
              ,d.Old_TIN
              ,d.Org_NPI
              ,d.Ind_NPI
              ,d.CCN
              ,d.PRG_Eff_Dt
              ,d.PRG_Term_Dt 
        FROM @tmpDetail AS d
        FOR XML PATH('Participant'),ROOT('Participants'),TYPE
      );
DECLARE @Tr XML=
      (
        SELECT t.[Trailer Code] AS TrailerCode
              ,t.[Preferred Provider List File Creation Date] AS FileCreationDate
              ,t.[Detail Record Count] AS RecordCount
        FROM @tmpTrailer AS t
        FOR XML PATH('Trailer')
      ); 

WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT @Hd,@Dt,@Tr
FOR XML PATH('ACOParticipantData');

Thank you for your response! The output of your script matches the sample file almost perfectly except for the top line. How would I go about adding to the top? — jaguin, May 11 '16 at 18:04
@jaguin , with `FOR XML PATH` it is possible to add so called "processing instructions'" to your XML (` Put something here ?>`), but unfortunately not outside of the XML. Read this: http://stackoverflow.com/q/33806454/5089204, it's a good workaround. And be aware, that SQL-Server cannot deal with UTF-8 directly. XML will always be converted to unicode. Are you sure, you need this line? — Shnugo, May 11 '16 at 18:48
I'm not sure that I absolutely need it, I just wanted to have it since the "processing instructions" were in the sample. I found a different workaround to add the XML declaration to the top by concatenating it and returning the XML as a varchar(max) type. I believe I am also suppose to return NULL values so I added ELEMENTS XSINIL to the @Dt XML but now it's declaring another namespace. I am currently trying to figure out how to get the NULLs in there without declaring another namespace since that was already done at the root level. — jaguin, May 12 '16 at 22:39
@jaguin If you let `FOR XML` work for you, it will create the needed namespaces over and over again. This is - technically spoken - no problem at all. It just blows up the file and makes it difficult to read... — Shnugo, May 12 '16 at 22:42

Creating an XML file containing Header, Detail, and Trailer sections with provided XSD file

1 Answers1