Я пытаюсь создать XML для возврата в BizTalk из хранимой процедуры SQL Server, что облегчит мне разделение файлов на основе числа Attribution_TIN
в нашей базе данных.
Мне нужны данные в формате XML, которые следуют этой структуре:
<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>
У меня есть рабочая таблица, из которой я извлекаю данные и использую следующий запрос FOR XML EXPLICIT для возврата результатов:
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;
Это работает для небольших наборов результатов, но как только результаты становятся больше, я получаю следующую ошибку:
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.
Я считаю, что знаю проблему, но не знаю, как ее исправить. Когда я запускаю приведенный выше запрос к своей рабочей таблице без использования FOR XML EXPLICIT, он возвращает следующие результаты:
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
Вот как должно выглядеть XML EXPLICIT для правильного форматирования результатов XML:
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
Что мне не хватает?
Я тоже пытался использовать FOR XML PATH
безрезультатно