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
Improve this question

3 Answers 3

Reset to default
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?

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

+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?
@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 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])
It does a join onto a lookup table to ensure values are within range, and counts how many are out of range.
@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...
|
0

In a full complain XQuery processor you could use just:

(/fd/field[@i=22])[1]/string-join(*,',')
Improve this answer

5 Comments

Doesn't work in SQL Server though. It gives the error The XQuery syntax '/function()' is not supported.
@Martin: Yes, but not even fn:string-join() is supported
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: 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)
Here's a list of MS SQL supported XQuery functions: msdn.microsoft.com/en-us/library/ms189254.aspx
0

You can do this entirely in XQuery:

  • Loop through the nodes you want using a for
  • concat a , to each text node.
  • Construct a new text node (if you don't do this step then you get an extra space).
  • Then pass that to a .value to transform from xml to nvarchar(max).
  • At this point it's all on big text node. But you need to remove the last comma using substring
select
  col1.query('
  for $i in fd/field[@i=22]/v
  return text{ concat(",", $i/text()[1]) }
  ').value('
  let $t := text()[1]
  return substring($t, 2)
  ', 'nvarchar(max)') 
from Temp12345;

db<>fiddle

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.