XML path not producing concatenated results in sql joined table

Question

I am trying to make a table that shows all the patients checked in to the hospital. I can join client, patient, check-in, appointment data just fine, but the alerts table has multiple rows which I am trying to aggregate/concatenate/rollup. I tried to create an XML statement but it doesn't seem to be working. I would like for all the alerts for the patient to be a single comma-separated string in one row. here is what I have:

select DISTINCT
    a.ResourceAbbreviation1, a.AppointmentType, a.StatusNum, c.sLastName,
    pt.Name, pt.WeightString, pt.AgeShort, pt.Breed, pt.Species, pt.Gender, pt.NewPatient,
    (select SUBSTRING((
        select ',' + al.stext AS 'data()'
        FOR XML PATH('')
        ), 2, 9999) as cautions),
    pt.Classification, p.kPatientId
from dbo.entpatients pt
join alerts al 
    on al.kpatientid = pt.IDPatient
join dbo.PatientCheckIns P
    on pt.IDPatient=p.kPatientId
join dbo.EntAppointments a 
    on a.IDPatient = p.kPatientId
join dbo.clients c 
    on c.kID=a.IDClient
where cast (a.StartTime as date) = cast(getdate() as date)
and a.StatusNum=4;

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Dec 21 '22 at 20:00

T N · Answer 1 · 2022-12-21T20:45:52.387

You need to move your alerts table reference inside the subselect as a FROM.

I also suggest using AS text() instead of AS data() (or omitting it entirely) to avoid unwanted spaces, and using STUFF() instead of SUBSTRING() to strip the leading comma. The extra nested SELECT is also unneeded.

select DISTINCT
    a.ResourceAbbreviation1, a.AppointmentType, a.StatusNum, c.sLastName,
    pt.Name, pt.WeightString, pt.AgeShort, pt.Breed, pt.Species, pt.Gender, pt.NewPatient,
    STUFF((
        select ',' + al.stext AS 'text()'
        from alerts al 
        where al.kpatientid = pt.IDPatient
        FOR XML PATH('')
        ), 1, 1, '') as cautions,
    pt.Classification,
    p.kPatientId
from dbo.entpatients pt
join dbo.PatientCheckIns P
    on pt.IDPatient=p.kPatientId
join dbo.EntAppointments a 
    on a.IDPatient = p.kPatientId
join dbo.clients c 
    on c.kID=a.IDClient
where cast (a.StartTime as date) = cast(getdate() as date)
and a.StatusNum=4;

If there is any chance that your alert text may contain special XML characters (such as <, >, or &) that might get encoded, I recommend a slightly modified form that uses the .value() function to extract the concatenated text.

    STUFF((
        select ',' + al.stext AS 'text()'
        from alerts al 
        where al.kpatientid = pt.IDPatient
        FOR XML PATH(''), TYPE
        ).value('text()[1]','nvarchar(max)'), 1, 1, '') as cautions,

This avoids seeing encodings like <, >, and & in the results. See this for more.

If you are using SQL server 2017 or later, you could also switch to the relatively new STRING_AGG() function. See here.

    (
        select STRING_AGG(',', al.stext)
        from alerts al 
        where al.kpatientid = pt.IDPatient
    ) as cautions,

I would also review your need for the DISTINCT. In some cases, it is appropriate when you knowingly expect your query to return duplicate rows that you wish to eliminate. For example, if you know you may have multiple visits by the same patient with identical selected data, DISTICT may be appropriate. However, if you have dropped it in to eliminate duplicates without knowing why, it may be a sign of an under-constrained join or other logic problems that warrant a further look.

XML path not producing concatenated results in sql joined table

1 Answers1