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
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.