SQL get comma separated values of a column from XML

Question

I am calling Scalar UDF from a stored procedure to get a column value. Inside the scalar UDF I have an xml and I have to get the comma separated values of a particular node. I am using Cross Apply but this is causing huge performance headache because stored procedure is actually used to get reports.

There is a table [Traveler] which has a field ID, BookingID and FareDetails. Inside the FareDetails we are storing the xml.

The logic inside UDF is as follows :

 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

The xml is as follows :

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

Output should be AP,AP

Please suggest better alternatives as calling the SP, which has this function with 10000 records is taking 15 secs.

Help would be appreciated.

There is [almost the same question here](http://stackoverflow.com/q/42570802/5089204)??? Whatever you (or somebody else) might need: Please read [How to ask a good SQL question](http://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056) and [How to create a MCVE](http://stackoverflow.com/help/mcve) — Shnugo, Mar 13 '17 at 14:54
[Now there is one more question](http://stackoverflow.com/q/42777070/5089204) with almost the same content ?? — Shnugo, Mar 14 '17 at 08:20

score 2 · Answer 1 · answered Mar 12 '17 at 15:50

2

Instead of using Scalar function convert this function into a inline-table valued function and use cross apply something like.....

Inline-table Valued Function

CREATE FUNCTION [dbo].[GetBookingInfo_NEW] (@BookingID bigint) 
RETURNS TABLE 
AS
RETURN (

        SELECT   t2.BookingID
              , (SELECT (PTSD.PSTDNode.value('(FBC)[1]', 'VARCHAR(1024)')  + ',') [text()]
                 FROM  [Traveler] t1
                 CROSS APPLY [FareDetails].nodes('/AirFareInfo/PTSDPFS/PTSD') PTSD(PSTDNode)
                 WHERE t2.BookingID = t1.BookingID
                 FOR XML PATH ('')
                 ) FareDetails
        FROM [Traveler] t2
        WHERE t2.BookingID = @BookingID
        )

Query

Select t.ID 
     , t.BookingID 
     , REVERSE(STUFF(REVERSE(c.[FareDetails]), 1, 1, ''))
FROM [Traveler] t
CROSS APPLY [dbo].[GetBookingInfo_NEW](t.BookingID) c

answered Mar 12 '17 at 15:50

M.Ali

67,945
13
101
127

Friend there are no of switch/case statements(5) inside that function , performing different logic and the code that I've written is part of one such switch case statement. – Ravish Kumar Mar 12 '17 at 15:58
2

@RavishKumar whatever maybe the logic, avoid using scalar functions, they get executed for each row returned by the outer query hence the slow performance, using inline table valued functions will convert this row-by-row to set based operation. If you your 4 different cases then use dynamic sql and use five different inline table valued functions. – M.Ali Mar 12 '17 at 16:03
M.Ali : I have modified the query : SELECT @InfoCSV = (SELECT TOP 1 REPLACE(FareDetails.query(N'data(/AirFareInfo/PTSDPFS/PTSD/FBC)').value('(text())[1]','nvarchar(100)'),' ',',') FROM [Traveler] WHERE [BookingID] = @BookingID) I am saving 8 secs – Ravish Kumar Mar 12 '17 at 17:07
It should be easier to place the `+','` at the beginning to avoid the double `REVERSE`. [There's a second (almost identical) question](http://stackoverflow.com/a/42780804/5089204), where I placed an answer using `XQuery`'s `data()`function, no need for expensive `APPLY` on `nodes()`... Might be interesting for you... – Shnugo Mar 14 '17 at 08:23

SQL get comma separated values of a column from XML

1 Answers1