title: FOR XML EXPLICIT Parent ID not among open tags
body: <p>I am trying to craft XML to return to BizTalk from a SQL Server stored procedure that will make it easy for me to debatch the files based on an <code>Attribution_TIN</code> number in our database.</p>
  
  <p>I need the data formatted in XML that follows this structure:</p>
  
  <pre><code>&lt;ns1:Destination xmlns:ns1="XXX.OptOut_PCPPharmacy_SQL" Attribution_TIN="001"&gt;
      &lt;Member PCP_ID="01" PCP_FullName="XXX" LastName="XXX" FirstName="XXX" Member_ID="XXX898XXX" Member_Gender="F" Member_Birth_Date="2011-11-08" Program_Name="xxx" Claim_Status="Paid Claim" Dispense_Date="2014-01-21" NDC_Number="000" Drug_Name="Ibuprofen 100 MG/5ML SUSP" Days_Supply="4" Dispensed_Quantity="100" PharmacyName="XXX" PrescribingName="XXX" /&gt;
      &lt;Member PCP_ID="02" PCP_FullName="XXX" LastName="XXX" FirstName="XXX" Member_ID="XXX898XXX" Member_Gender="F" Member_Birth_Date="2011-11-08" Program_Name="xxx" Claim_Status="Paid Claim" Dispense_Date="2014-01-21" NDC_Number="000" Drug_Name="Ibuprofen 100 MG/5ML SUSP" Days_Supply="4" Dispensed_Quantity="100" PharmacyName="XXX" PrescribingName="XXX" /&gt;
  &lt;/ns1:Destination&gt;
  &lt;ns1:Destination xmlns:ns1="XXX.OptOut_PCPPharmacy_SQL" Attribution_TIN="002"&gt;
      &lt;Member PCP_ID="01" PCP_FullName="XXX" LastName="XXX" FirstName="XXX" Member_ID="XXX898XXX" Member_Gender="F" Member_Birth_Date="2011-11-08" Program_Name="xxx" Claim_Status="Paid Claim" Dispense_Date="2014-01-21" NDC_Number="000" Drug_Name="Ibuprofen 100 MG/5ML SUSP" Days_Supply="4" Dispensed_Quantity="100" PharmacyName="XXX" PrescribingName="XXX" /&gt;
      &lt;Member PCP_ID="02" PCP_FullName="XXX" LastName="XXX" FirstName="XXX" Member_ID="XXX898XXX" Member_Gender="F" Member_Birth_Date="2011-11-08" Program_Name="xxx" Claim_Status="Paid Claim" Dispense_Date="2014-01-21" NDC_Number="000" Drug_Name="Ibuprofen 100 MG/5ML SUSP" Days_Supply="4" Dispensed_Quantity="100" PharmacyName="XXX" PrescribingName="XXX" /&gt;
  &lt;/ns1:Destination&gt;
  </code></pre>
  
  <p>I have a worktable that I am pulling data out of and using the following FOR XML EXPLICIT query to return results:</p>
  
  <pre><code>SELECT 1 AS Tag
        ,NULL AS Parent
        ,'XXX.OptOut_PCPPharmacy_SQL'  AS         [ns1:Destination!1!xmlns:ns1]
        ,PCP1.Attribution_TIN                                     AS [ns1:Destination!1!Attribution_TIN]
        ,NULL                                                     AS [Member!2!PCP_ID]
        ,NULL                                                     AS [Member!2!PCP_FullName]
        ,NULL                                                     AS [Member!2!LastName]
        ,NULL                                                     AS [Member!2!FirstName]
        ,NULL                                                     AS [Member!2!Member_ID]
        ,NULL                                                     AS [Member!2!Member_Gender]
        ,NULL                                                     AS [Member!2!Member_Birth_Date]
        ,NULL                                                     AS [Member!2!Program_Name]
        ,NULL                                                     AS [Member!2!Claim_Status]
        ,NULL                                                     AS [Member!2!Dispense_Date]
        ,NULL                                                     AS [Member!2!NDC_Number]
        ,NULL                                                     AS [Member!2!Drug_Name]
        ,NULL                                                     AS [Member!2!Days_Supply]
        ,NULL                                                     AS [Member!2!Dispensed_Quantity]
        ,NULL                                                     AS [Member!2!PharmacyName]
        ,NULL                                                     AS [Member!2!PrescribingName]
    FROM WorkTable AS PCP1
   UNION
  SELECT 2 AS Tag
        ,1 AS Parent
        ,'XXX.OptOut_PCPPharmacy_SQL'                    
        ,PCP2.Attribution_TIN
        ,PCP2.PCP_ID                                         
        ,PCP2.PCP_Fullname
        ,PCP2.LastName
        ,PCP2.FirstName                          
        ,PCP2.Member_ID
        ,PCP2.Member_Gender       
        ,PCP2.Member_Birth_Date
        ,PCP2.[Program_Name]                                  
        ,PCP2.Claim_Status                                               
        ,PCP2.Dispense_Date                                  
        ,PCP2.NDC_Number                                     
        ,PCP2.Drug_Name                                  
        ,PCP2.Days_Supply                                    
        ,FLOOR(PCP2.Dispensed_Quantity)
        ,PCP2.PharmacyName                           
        ,PCP2.PrescribingName   
    FROM WorkTable AS PCP2 
      ORDER BY [ns1:Destination!1!xmlns:ns1]
           ,[ns1:Destination!1!Attribution_TIN]
  FOR XML EXPLICIT;
  </code></pre>
  
  <p>This works for small result sets but as soon as the results get bigger I get the following error:</p>
  
  <pre><code>Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.
  </code></pre>
  
  <p>I believe I know the problem but am unsure how to fix it. When I run the above query against my worktable without using FOR XML EXPLICIT it returns results as follows:</p>
  
  <pre><code>Tag     Parent  ns1:Destination!1!xmlns:ns1             ns1:Destination!1!Attribution_TIN       Member!2!PCP_ID         Member!2!PCP_FullName   
  2   1   XXX.OptOut_PCPPharmacy_SQL  010924601               XXX         XXX 
  2   1   XXX.OptOut_PCPPharmacy_SQL  010924601               XXX         XXX
  </code></pre>
  
  <p>This is what it should look like for XML EXPLICIT to format the XML results correctly:</p>
  
  <pre><code>Tag     Parent  ns1:Destination!1!xmlns:ns1             ns1:Destination!1!Attribution_TIN       Member!2!PCP_ID         Member!2!PCP_FullName
  1   NULL    XXX.OptOut_PCPPharmacy_SQL  010924601               NULL            NULL    
  2   1   XXX.OptOut_PCPPharmacy_SQL  010924601               XXX         XXX 
  2   1   XXX.OptOut_PCPPharmacy_SQL  010924601               XXX         XXX
  </code></pre>
  
  <p>What am I missing?</p>
  
  <p>I've tried using <code>FOR XML PATH</code> to no avail as well</p>