title: Creating an XML file containing Header, Detail, and Trailer sections with provided XSD file
body: <p>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:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>But I need to produce something more similar to this provided sample XML file:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="no" ?&gt;
  &lt;ACOParticipantData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;Header&gt;
      &lt;HeaderCode&gt;HDR_PFPRVDR&lt;/HeaderCode&gt;
      &lt;FileCreationDate&gt;20160101&lt;/FileCreationDate&gt;
      &lt;ACOProgCode&gt;21&lt;/ACOProgCode&gt;
    &lt;/Header&gt;
    &lt;Participants&gt;
      &lt;Participant&gt;
        &lt;ACO_ID&gt;V199&lt;/ACO_ID&gt;
        &lt;TIN&gt;123456789&lt;/TIN&gt;
        &lt;Old_TIN&gt;987654321&lt;/Old_TIN&gt;
        &lt;Org_NPI&gt;1234567890&lt;/Org_NPI&gt;
        &lt;Ind_NPI&gt;1234567890&lt;/Ind_NPI&gt;
        &lt;CCN&gt;123456&lt;/CCN&gt;
        &lt;PRG_Eff_Dt&gt;20160101&lt;/PRG_Eff_Dt&gt;
        &lt;PRG_Term_Dt&gt;20161231&lt;/PRG_Term_Dt&gt;
      &lt;/Participant&gt;
    &lt;/Participants&gt;
    &lt;Trailer&gt;
      &lt;TrailerCode&gt;TRL_PFPRVDR&lt;/TrailerCode&gt;
      &lt;FileCreationDate&gt;20160101&lt;/FileCreationDate&gt;
      &lt;RecordCount&gt;1&lt;/RecordCount&gt;
    &lt;/Trailer&gt;
  &lt;/ACOParticipantData&gt;Sample 
  </code></pre>
  
  <p>Here is the sample XSD file:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;           
  &lt;xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="qualified"&gt;
  &lt;xsd:element name="Header" type="HeaderType"/&gt;
          &lt;xsd:element name="Participants" type="ParticipantsType"/&gt;
          &lt;xsd:element name="Participant" type="ParticipantType"/&gt;
          &lt;xsd:element name="Trailer" type="TrailerType"/&gt;
  
          &lt;xsd:element name="ACOParticipantData"&gt;                
      &lt;xsd:complexType&gt;
                                  &lt;xsd:sequence&gt;
              &lt;xsd:element ref="Header"/&gt;
                                              &lt;xsd:element ref="Participants" minOccurs="0" maxOccurs="1"/&gt;
                                              &lt;xsd:element ref="Trailer"/&gt;
                                  &lt;/xsd:sequence&gt;
                      &lt;/xsd:complexType&gt;
          &lt;/xsd:element&gt;
  
          &lt;xsd:complexType name="HeaderType"&gt;
                      &lt;xsd:sequence&gt;
                                  &lt;xsd:element name="HeaderCode" type="HeaderCodeENUM"/&gt;
                                  &lt;xsd:element name="FileCreationDate"&gt;
              &lt;xsd:simpleType&gt;
                                                          &lt;xsd:restriction base="xsd:string"/&gt;
                                              &lt;/xsd:simpleType&gt;
                                  &lt;/xsd:element&gt;
                                  &lt;xsd:element name="ACOProgCode" type="ACOProgCodeType"/&gt;                                    
                      &lt;/xsd:sequence&gt;
          &lt;/xsd:complexType&gt;
  
          &lt;xsd:complexType name="ParticipantsType"&gt;
                      &lt;xsd:sequence&gt;
                                  &lt;xsd:element ref="Participant" maxOccurs="unbounded"/&gt;
                      &lt;/xsd:sequence&gt;
          &lt;/xsd:complexType&gt;
  
          &lt;xsd:complexType name="ParticipantType"&gt;                   
      &lt;xsd:sequence&gt;  
          &lt;xsd:element name="ACO_ID" type="OrgType"/&gt;
          &lt;xsd:element name="TIN" type="xsd:string"/&gt;
          &lt;xsd:element name="Old_TIN" nillable="true" type="xsd:string"/&gt;
          &lt;xsd:element name="Org_NPI" nillable="true" type="xsd:string"/&gt;
          &lt;xsd:element name="Ind_NPI" nillable="true" type="xsd:string"/&gt;
          &lt;xsd:element name="CCN" nillable="true" type="xsd:string"/&gt;
          &lt;xsd:element name="PRG_Eff_Dt" type="DateType"/&gt;
          &lt;xsd:element name="PRG_Term_Dt" nillable="true" type="xsd:string"/&gt;
      &lt;/xsd:sequence&gt;
          &lt;/xsd:complexType&gt;
  
          &lt;xsd:complexType name="TrailerType"&gt;
      &lt;xsd:sequence&gt;
                                  &lt;xsd:element name="TrailerCode" type="TrailerCodeENUM"/&gt;
                                  &lt;xsd:element name="FileCreationDate"&gt;                                     
                                              &lt;xsd:simpleType&gt;
                                                          &lt;xsd:restriction base="xsd:string"/&gt;
                                              &lt;/xsd:simpleType&gt;
                                  &lt;/xsd:element&gt;                         
                                  &lt;xsd:element name="RecordCount"&gt;
                                              &lt;xsd:simpleType&gt;
                                                          &lt;xsd:restriction base="xsd:integer"&gt;
                                                                      &lt;xsd:minInclusive value="0"/&gt;
                                                                      &lt;xsd:maxInclusive value="9999999"/&gt;
                                                          &lt;/xsd:restriction&gt;
                                              &lt;/xsd:simpleType&gt;
                                  &lt;/xsd:element&gt;                         
                      &lt;/xsd:sequence&gt;
          &lt;/xsd:complexType&gt;
  
          &lt;xsd:simpleType name="HeaderCodeENUM"&gt;
                      &lt;xsd:restriction base="xsd:string"&gt;
                         &lt;xsd:pattern value="HDR_PFPRVDR"/&gt;
                      &lt;/xsd:restriction&gt;
          &lt;/xsd:simpleType&gt;
  
          &lt;xsd:simpleType name="ACOProgCodeType"&gt;
                      &lt;xsd:restriction base="xsd:string"&gt;
                         &lt;xsd:pattern value="21"/&gt;
                      &lt;/xsd:restriction&gt;
          &lt;/xsd:simpleType&gt;
  
          &lt;xsd:simpleType name="TrailerCodeENUM"&gt;
                      &lt;xsd:restriction base="xsd:string"&gt;
                                  &lt;xsd:pattern value="TRL_PFPRVDR"/&gt;
                      &lt;/xsd:restriction&gt;
          &lt;/xsd:simpleType&gt;
  
          &lt;xsd:simpleType name="DateType"&gt;
                      &lt;xsd:restriction base="xsd:string"&gt;
                                  &lt;xsd:pattern value="\d{8}"/&gt;
                      &lt;/xsd:restriction&gt;
          &lt;/xsd:simpleType&gt;
  
          &lt;xsd:simpleType name="OrgType"&gt;
                      &lt;xsd:restriction base="xsd:string"&gt;
                                  &lt;xsd:pattern value="V\d{3}"/&gt;
                      &lt;/xsd:restriction&gt;
          &lt;/xsd:simpleType&gt;
  
  &lt;/xsd:schema&gt;
  </code></pre>
  
  <p>Any feedback would be greatly appreciated, thank you!</p>