Получить разделенные запятыми значения из XML в SQL

Question

Получить разделенные запятыми значения из XML в SQL

Я вызываю Scalar UDF из хранимой процедуры, чтобы получить значение столбца. Внутри скалярного UDF у меня есть xml, и я должен получить значения через запятую определенного узла. Я использовал Cross apply, но это вызвало огромные проблемы с производительностью, потому что хранимая процедура фактически используется для получения отчетов.

Есть таблица [Traveler], которая имеет идентификатор поля, BookingID(может быть дублирован) и FareDetails. Внутри FareDetails мы храним XML.

Логика внутри UDF следующая: 1-е решение, с использованием перекрестного применения:

 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-е решение без перекрестного применения:

  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

Второе решение экономит много времени, но когда у нас есть дубликаты идентификаторов бронирования, оно не объединяет все значения FBC . Например: 1) Если BookingID является уникальным, и у нас есть FareDetail xml, как показано ниже, то вывод должен быть AP,AP 2) Если BookingID не является уникальным (приходит дважды), и у нас есть FareDetail xml, как показано ниже, то вывод должен быть AP,AP,AP,AP, соответствующий обоим BookingID. XML выглядит следующим образом:

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

Пожалуйста, предложите, как это можно сделать, помня о производительности.

0

sql sqlperformance sqlxml cross-apply

Источник

user7646597 14 мар '17 в 03:05

1 ответ

Решение

Другие вопросы по тегам sql sqlperformance sqlxml cross-apply

user5089204 14 мар '17 в 08:13 2017-03-14 08:13 · Accepted Answer · 2017-03-14 08:13

Это полностью рабочий пример.

Вы сказали нам, что производительность имеет значение, поэтому не используйте скалярный UDF!

Попробуй вот так (в следующий раз твоя задача - создать (уменьшенный!!!) MCVE:

CREATE DATABASE testDB;
GO
USE testDB;
GO
CREATE TABLE Booking(BookingID INT CONSTRAINT PK_Booking PRIMARY KEY
                    ,SomeBookingData VARCHAR(100));
INSERT INTO Booking VALUES(1,'Booking 1'),(2,'Booking 2');

CREATE TABLE BookingInfo(BookingID INT CONSTRAINT FK_BookingInfo_BookingID FOREIGN KEY REFERENCES Booking(BookingID)
                        ,SomeOtherInfo VARCHAR(100)
                        ,FareDetails XML);
INSERT INTO BookingInfo VALUES
 (1,'First row for ID=1, returns AP,AP'
 ,N'<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AP</FBC>
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AP</FBC>
    </PTSD>
  </PTSDPFS>
</AirFareInfo>')
,(1,'Second row for ID=1, returns XY,MN'
 ,N'<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>XY</FBC>
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>MN</FBC>
    </PTSD>
  </PTSDPFS>
</AirFareInfo>')
,(2,'row with ID=2, returns AA,BB'
 ,N'<AirFareInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IPFA="false">
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>AA</FBC>
    </PTSD>
  </PTSDPFS>
  <PTSDPFS>
    <PTSD IO="false">
      <FBC>BB</FBC>
    </PTSD>
  </PTSDPFS>
</AirFareInfo>');
GO

- Это функция. Возвращается as table и полностью встроенный (нет BEGIN...END!)

CREATE FUNCTION dbo.CreateBookingInfoCSV(@BookingID INT)
RETURNS TABLE
AS
RETURN
SELECT STUFF(
(
    SELECT ','+REPLACE(FareDetails.query(N'data(/AirFareInfo/PTSDPFS/PTSD/FBC)').value(N'.',N'nvarchar(max)'),' ',',')
    FROM BookingInfo AS bi
    WHERE bi.BookingID=@BookingID
    FOR XML PATH('')
),1,1,'') AS BookingInfoCSV;
GO

-Подсказка XQuery data() function сломается, если ваши значения содержат пробелы!

--Следующие SELECT вызывает все строки из Bookingи получает подходящие детали

SELECT b.BookingID
      ,b.SomeBookingData
      ,A.BookingInfoCSV 
FROM Booking AS b
OUTER APPLY dbo.CreateBookingInfoCSV(b.BookingID) AS A;
GO

- Очистить (осторожно с реальными данными!)

USE master;
GO
DROP DATABASE testDB;

--Результат

BookingID   SomeBookingData BookingInfoCSV
1           Booking 1       AP,AP,XY,MN
2           Booking 2       AA,BB