Get Comma Separated Values From An Xml In Sql

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 used

Solution 1:

This is a fully working example.

You told us, that performance matters, so do not use scalar UDF!

Try it like this (next time it's your job to create a (reduced!!!) 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'<AirFareInfoxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema"IPFA="false"><PTSDPFS><PTSDIO="false"><FBC>AP</FBC></PTSD></PTSDPFS><PTSDPFS><PTSDIO="false"><FBC>AP</FBC></PTSD></PTSDPFS></AirFareInfo>')
,(1,'Second row for ID=1, returns XY,MN'
 ,N'<AirFareInfoxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema"IPFA="false"><PTSDPFS><PTSDIO="false"><FBC>XY</FBC></PTSD></PTSDPFS><PTSDPFS><PTSDIO="false"><FBC>MN</FBC></PTSD></PTSDPFS></AirFareInfo>')
,(2,'row with ID=2, returns AA,BB'
 ,N'<AirFareInfoxmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:xsd="http://www.w3.org/2001/XMLSchema"IPFA="false"><PTSDPFS><PTSDIO="false"><FBC>AA</FBC></PTSD></PTSDPFS><PTSDPFS><PTSDIO="false"><FBC>BB</FBC></PTSD></PTSDPFS></AirFareInfo>');
GO

--This is the function. It returns as table and is fully inlined (no BEGIN...END!)

CREATEFUNCTIONdbo.CreateBookingInfoCSV(@BookingID INT)
RETURNSTABLEASRETURNSELECTSTUFF(
(
    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,'') ASBookingInfoCSV;
GO

--Hint the trick with XQuery data() function will break, if your values contain blanks!

--The following SELECT calls all rows from Booking-table and gets the fitting details

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

--Clean up (carefull with real data!)

USEmaster;
GO
DROP DATABASE testDB;

--The result

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