Suppose there is a table (SQL Server 2019) with two columns. Both are nvarchar but the ResultDoc
column happens to store xml. Example data:
Name | ResultDoc |
---|---|
Sam | <doc><results><result>a</result><result>b</result><result>x</result></results></doc> |
Jan | <doc><results><result>c</result><result type="pending">z</result><result>m</result><result>k</result></results></doc> |
I want to be able to query this table, filtering on the xml, and get a result like this:
Name | Results |
---|---|
Sam | a, b, x |
Jan | c, m, k |
I've seen examples that do this for a single document, but I haven't been able to work a solution like that into a query that does this for several rows.
CodePudding user response:
Updated ... moved the aggregate into the OUTER APPLY
Since you are on 2019, you can use string_agg()
Example or dbFiddle
Select A.Name
,C.Results
From YourTable A
Outer Apply ( values (try_convert(xml,[ResultDoc]) ) )B(xmlData)
Outer Apply (
Select Results = string_Agg(xn.value('./text()[1]', 'nvarchar(150)'),',')
From xmlData.nodes('doc/results/result') C(xn)
Where coalesce(xn.value('@type[1]', 'nvarchar(150)'),'') not in ('pending')
) C
Results
Name Results
Jan c,m,k
Sam a,b,x