ЗА XML EXPLICIT Идентификаторът на родител не е сред отворените тагове

Опитвам се да създам 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 без резултат


xml sql-server explicit for-xml-explicit
person Ritley572    schedule 01.02.2014    source източник

Отговори (1)


arrow_upward
1
arrow_downward

След като направих още известно копаене, се оказа, че се опитвах да вложа 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');

Сега трябва да разбера как да накарам заявката да се изпълнява по-бързо с по-големи набори от резултати (над 100 000). В момента отнема около 20 минути, за да работи при 97k записа.

person Ritley572    schedule 02.02.2014