7

I have a data stored in a xml column and need a comma-separated list of child nodes. Using script below, I can only get "A B C". Please help me get "A,B,C" using xquery (Simple replace of space with comma does not help because we have data with spaces inside).

create table Temp12345 (col1 xml)
go

insert into Temp12345 (col1)
values('<fd><field i="22"><v>A</v><v>B</v><v>C</v></field></fd>')
go

select col1.value('(/fd/field[@i=22])[1] ', 'NVarchar(Max)') 
from Temp12345
go

drop table Temp12345
go
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ben
  • 2,454
  • 26
  • 32

2 Answers2

10

Try this:

SELECT
    STUFF((SELECT 
              ',' + fd.v.value('(.)[1]', 'varchar(10)')
           FROM 
              Temp12345
           CROSS APPLY
              col1.nodes('/fd/field/v') AS fd(v)
           FOR XML PATH('')
          ), 1, 1, '')

This gives me A,B,C - does it work for you, too?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • +1 for a working answer. Out of curiosity, I see you're using CROSS APPLY. I have done something similar with a CTE. Is there any significant advantage to either approach? – IamIC Dec 07 '10 at 16:50
  • @IanC: not sure - would have to see your CTE :-) That's just the way I know I can "shred" a list of XML nodes into a "pseudo-table" of row, to grab individual bits and pieces from it. – marc_s Dec 07 '10 at 16:52
  • @marc not the same application, but it queries the XML in a similar way: WITH t AS ( SELECT x.node.value('fn:local-name(.)', 'varchar(50)') AS RankItem, x.node.value('(.)', 'float') AS Value FROM @xml.nodes('/data/*') AS x(node) -- select *direct* children of the root 'data' node ) SELECT @OutOfBounds = Count(t.RankItem) FROM t INNER JOIN [int].RankItems AS ri ON ri.RankItem = t.RankItem WHERE (t.Value < ri.[Min] OR t.Value > ri.[Max]) – IamIC Dec 07 '10 at 16:54
  • It does a join onto a lookup table to ensure values are within range, and counts how many are out of range. – IamIC Dec 07 '10 at 16:55
  • @IanC: works great on a single XML variable - but how are you going to extend that to include several rows which each have a XML column?? That's where the `CROSS APPLY` really shines... – marc_s Dec 07 '10 at 17:06
  • @Marc the input XML, for my use case, will always be in the format of 1 column, several rows. I'm not having any problems with 10 rows. Each is correctly evaluated. However, I am interested if CROSS APPLY is a better approach. – IamIC Dec 07 '10 at 17:09
  • @IanC: but then you're somehow looping over your 10 rows, and calling your CTE for each entry, right?? Cross Apply works against any number of rows at once – marc_s Dec 07 '10 at 17:12
  • @Marc my understanding of what I pasted is the CTE is a table, which is the result of a 1-time conversion of the XML, and I then process against this table by joining it to a reference table and evaluating the results. I don't see why it would be looping. – IamIC Dec 07 '10 at 17:26
  • @Marc per http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/, it seems to me that I am understanding this correctly. There is no recursion here. – IamIC Dec 07 '10 at 18:05
0

In a full complain XQuery processor you could use just:

(/fd/field[@i=22])[1]/string-join(*,',')
  • Doesn't work in SQL Server though. It gives the error `The XQuery syntax '/function()' is not supported.` – Martin Smith Dec 07 '10 at 17:10
  • @Martin: Yes, but not even `fn:string-join()` is supported –  Dec 07 '10 at 17:18
  • It supports `concat` but I couldn't figure out any useful way of using it. `'concat((/fd/field[@i=22])[1],'','')'` just gives `ABC,` – Martin Smith Dec 07 '10 at 17:20
  • @Martin: Each argument of `fn:concat()` function should be an instance of `xs:string` data type. So, you can't use sequence. That only leaves a "static" solution like `concat(v[1],',',v[2],',',v[3])`. I have no SQL Server instance to test, so I don't know how it deals with sequence responso like `for $item in (/fd/field[@i=22])[1]/* return if ($item/following-sibling::node()[1]) then concat($item,',') else string($item)` –  Dec 07 '10 at 19:57
  • Here's a list of MS SQL supported XQuery functions: https://msdn.microsoft.com/en-us/library/ms189254.aspx – JohnLBevan Sep 19 '16 at 14:38