Obter valores separados por vírgula de um xml no SQL
Eu estou chamando Scalar UDF de um procedimento armazenado para obter um valor de coluna. Dentro da UDF escalar, tenho um xml e preciso obter os valores separados por vírgula de um nó específico. Usei o Cross aplique, mas causou um grande gargalo de desempenho porque o procedimento armazenado é realmente usado para buscar relatórios.
Há uma tabela [Traveler] que possui um ID de campo, BookingID (pode ser duplicado) e FareDetails. Dentro do FareDetails, estamos armazenando o xml.
A lógica dentro do UDF é a seguinte: 1st Solution, Using Cross Apply:
ALTER FUNCTION [dbo].[GetBookingInfo] (@BookingID bigint, @InfoID smallint) RETURNS VARCHAR(1024) AS
BEGIN
DECLARE @InfoCSV VARCHAR(1024)
--
-- Fare Basis: InfoID = 1
--
IF @InfoID = 1
BEGIN
SELECT @InfoCSV = (SELECT
(PTSD.PSTDNode.value('(FBC)[1]', 'VARCHAR(1024)') + ',') [text()]
FROM
[Traveler]
CROSS APPLY [FareDetails].nodes('/AirFareInfo/PTSDPFS/PTSD') PTSD(PSTDNode)
WHERE
[BookingID] = @BookingID
ORDER BY
ID ASC
FOR XML PATH (''))
IF @InfoCSV IS NOT NULL AND LEN(@InfoCSV) > 0
SET @InfoCSV = LEFT(@InfoCSV, LEN(@InfoCSV) - 1)
END
RETURN @InfoCSV
2ª solução, sem aplicação cruzada:
ALTER FUNCTION [dbo].[GetBookingInfo] (@BookingID bigint, @InfoID smallint) RETURNS VARCHAR(1024) AS
BEGIN
DECLARE @InfoCSV VARCHAR(1024)
--
-- Fare Basis: InfoID = 1
--
IF @InfoID = 1
BEGIN
SELECT @InfoCSV = (SELECT TOP 1 REPLACE(FareDetails.query(N'data(/AirFareInfo/PTSDPFS/PTSD/FBC)').value('(text())[1]','nvarchar(100)'),' ',',')
FROM [Traveler]
WHERE
[BookingID] = @BookingID)
IF @InfoCSV IS NOT NULL AND LEN(@InfoCSV) > 0
SET @InfoCSV = LEFT(@InfoCSV, LEN(@InfoCSV) - 1)
END
RETURN @InfoCSV
A segunda solução está economizando muito tempo, mas quando temos IDs de reserva duplicados, não concatenamos todos os valores do FBC. por exemplo. : 1) Se o BookingID for único e tivermos o FareDetail xml da seguinte forma, a saída deverá ser AP, AP 2) Se o BookingID não for exclusivo (vir duas vezes) e tivermos o FareDetail xml da seguinte maneira, o output deverá ser AP, AP, AP , AP correspondente a ambos BookingID. O xml é o seguinte:
<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
<PT>Flight</PT>
<FPMID>0</FPMID>
<PTID>1</PTID>
<FS>
<CID>2</CID>
<Value>0</Value>
</FS>
<TF>
<CID xsi:nil="true" />
<Value>0</Value>
</TF>
<VF>
<CID>2</CID>
<Value>0</Value>
</VF>
<VD>
<CID>2</CID>
<Value>0</Value>
</VD>
<VCR xsi:nil="true" />
<VC>
<CID>2</CID>
<Value>0</Value>
</VC>
<VFC>
<CID>2</CID>
<Value>0</Value>
</VFC>
<VST />
<VIT />
<AAPFVDR xsi:nil="true" />
<CC>
<CID>2</CID>
<Value>0</Value>
</CC>
<D>
<CID>2</CID>
<Value>514.15</Value>
</D>
<PD>
<CID>2</CID>
<Value>0</Value>
</PD>
<EBF>
<CID>2</CID>
<Value>0</Value>
</EBF>
<CST>
<DL>
<ATRID>13</ATRID>
<OB>
<CID>2</CID>
<Value>74.04</Value>
</OB>
<OC>
<CID>2</CID>
<Value>0.00</Value>
</OC>
<OS>
<CID>2</CID>
<Value>0.00</Value>
</OS>
<OF>
<CID>2</CID>
<Value>50.83</Value>
</OF>
<OP>
<CID>2</CID>
<Value>0.00</Value>
</OP>
<C>
<CID>2</CID>
<Value>0</Value>
</C>
<IBF>false</IBF>
<D>2014-06-09T14:57:53.521Z</D>
</DL>
</CST>
<CIT />
<CRMR xsi:nil="true" />
<CRM>
<CID>2</CID>
<Value>0</Value>
</CRM>
<TL ATC="Tax" PC="" DEN="User Development Fee - Arrival (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>75.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Passenger Service Fee">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>146.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="User Development Fee - Departure (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>1681.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Cute Fee">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>50.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Government Service Tax">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>151.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="User Development Fee - Arrival (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>833.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Passenger Service Fee">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>1132.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="User Development Fee - Departure (UDF)">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>76.00</Value>
</Amount>
</TL>
<TL ATC="Tax" PC="" DEN="Government Service Tax">
<TID xsi:nil="true" />
<Amount>
<CID>2</CID>
<Value>148.00</Value>
</Amount>
</TL>
<PTSDPFS>
<PTSD IO="false">
<FBC>AP</FBC>
<ACD RBD="" ACCID="1" MCT="Super Sale Fare(AP)" INC="false" />
<ATSID xsi:nil="true" />
</PTSD>
</PTSDPFS>
<PTSDPFS>
<PTSD IO="false">
<FBC>AP</FBC>
<ACD RBD="" ACCID="1" MCT="Super Sale Fare(AP)" INC="false" />
<ATSID xsi:nil="true" />
</PTSD>
</PTSDPFS>
<RuleDetails>
<TRS xsi:nil="true" />
<PP xsi:nil="true" />
<II xsi:nil="true" />
<LTD xsi:nil="true" />
</RuleDetails>
</AirFareInfo>
Sugira como isso pode ser feito, mantendo o desempenho em mente.