我正在尝试从SQL Server存储的过程中返回到BizTalk,这将使我很容易根据数据库中的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>
我有一个可工作的方法,我将数据从XML明确查询中使用以下内容来返回结果:
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.
我相信我知道这个问题,但不确定如何解决。当我在不使用XML的情况下对工作台上运行上述查询时,它将返回结果如下:
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明确格式化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
也无效
做了更多挖掘后,我试图用 FOR XML PATH
完全错误地嵌套,这就是为什么它对我不起作用的原因。我能够使用此查询来实现所需的结果:
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');
现在,我需要弄清楚如何使查询使用较大的结果集(超过100000)更快地运行。现在,在97k记录中运行大约需要20分钟。