FOR XML EXPLICIT Parent ID not among open tags

Question

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 Attribution_TIN number in our database.

I need the data formatted in XML that follows this structure:

<ns1:Destination xmlns:ns1="XXX.OptOut_PCPPharmacy_SQL" Attribution_TIN="001">
    <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" />
    <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" />
</ns1:Destination>
<ns1:Destination xmlns:ns1="XXX.OptOut_PCPPharmacy_SQL" Attribution_TIN="002">
    <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" />
    <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" />
</ns1:Destination>

I have a worktable that I am pulling data out of and using the following FOR XML EXPLICIT query to return results:

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;

This works for small result sets but as soon as the results get bigger I get the following error:

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.

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:

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

This is what it should look like for XML EXPLICIT to format the XML results correctly:

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

What am I missing?

I've tried using FOR XML PATH to no avail as well

Answer 1

After doing some more digging it turns out I was trying to nest with FOR XML PATH completely wrong which is why it wasn't working for me. I was able to achieve the desired results using this query:

WITH XMLNAMESPACES (
'http://XXX.OptOut_PCPPharmacy_SQL' as ns1
)
SELECT  Q1.Attribution_Tin AS '@Attribution_TIN',
(SELECT PCP_ID                      AS '@PCP_ID'                                           
      ,PCP_Fullname                 AS '@PCP_FullName'
      ,LastName                     AS '@LastName'
      ,FirstName                    AS '@FirstName'                    
      ,Member_ID                    AS '@Member_ID'
      ,Member_Gender                AS '@Member_Gender'     
      ,Member_Birth_Date            AS '@Member_Birth_Date'
      ,[Program_Name]               AS '@Program_Name'                                
      ,Claim_Status                 AS '@Claim_Status'                                         
      ,Dispense_Date                AS '@Dispense_Date'                                
      ,NDC_Number                   AS '@NDC_Number'                                   
      ,Drug_Name                    AS '@Drug_Name'                                
      ,Days_Supply                  AS '@Days_Supply'                                  
      ,FLOOR(Dispensed_Quantity)    AS '@Dispensed_Quantity'
      ,PharmacyName                 AS '@PharmacyName'                 
      ,PrescribingName              AS '@PrescribingName'
  FROM WorkTable AS Q2
  WHERE Q2.Attribution_TIN = Q1.Attribution_TIN
  FOR XML PATH ('Member'), TYPE
)
FROM WorkTable AS Q1
GROUP BY Attribution_TIN
FOR XML PATH ('ns1:Destination');

Now I need to figure out how to get the query to run faster with larger result sets (over 100000). Right now it takes about 20 minutes to run at 97k records.