2

This question follows up this great answer: T-SQL XML Query, how to seperate matching nodes into individual rows? What if the values where:

   <child>
    <name>Fred</name>
    <sname>Flintstone</name>
   </child>
   <child>
    <name>Bill</name>
    <sname>Gates</name>
   </child>

And I wanted the output to be like:

Fred
Flintstone
Bill
Gates

Or even better, this:

name: Fred
sname: Flintstone
name: Bill
sname: Gates

(all in one column)

-->Since I can't answer my own question for the next 3 hours, I'll edit my question as suggested by stackoverflow. Here's my answer to my own question:

I've figured it out! :-) So I'm obliged to share my own solution. Here it is:

SELECT
  distinct childs.value('fn:local-name(.)', 'nvarchar(50)') + '=' + childs.value('(text())[1]', 'varchar(50)') as Children
FROM  
  #t CROSS APPLY
  data.nodes('//parent/child/*') AS Children(childs)

Thanks anyone for having a look at my question!

Improve this question
1
  • 1
    Your question was not clear. marc_s's solution will produce desired output. Also, the last answer, Mikael Eriksson's answer is published 12m before you edited your question (by adding your solution). I think you should choose one of these answers as a solution for your question. Commented May 5, 2012 at 16:49

2 Answers 2

Reset to default
2 
declare @XML xml
set @XML = 
'<child>
  <name>Fred</name>
  <sname>Flintstone</sname>
 </child>
 <child>
   <name>Bill</name>
   <sname>Gates</sname>
 </child>'

select N.value('concat(local-name(.),": ",.)', 'varchar(max)')
from @XML.nodes('/child/*') as T(N)

Result:

name: Fred 
sname: Flintstone 
name: Bill 
sname: Gates

Update:
Using a table and a guaranteed order by

declare @XML xml
set @XML = 
'<child>
  <name>Fred</name>
  <sname>Flintstone</sname>
 </child>
 <child>
   <name>Bill</name>
   <sname>Gates</sname>
 </child>'

declare @T table (ID int identity primary key, XMLColumn xml)
insert into @T values(@XML)
insert into @T values(@XML)

select ID,
       Names
from
  (
    select ID,
           N.value('concat(local-name(.),": ",.)', 'varchar(max)') as Names,
           row_number() over(partition by ID order by T.N) as rn
    from @T
      cross apply XMLColumn.nodes('/child/*') as T(N)
  ) T
order by ID, rn
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Concerning the order of the result, this is even better. Thanks Mikael!
@stakes Without a order by the order of the rows is arbitrary. There is no guarantee that the order will always be what you see not. What order do you want? There is a trick if you want to the order the nodes appear in the XML.
I want the order to be like you posted, the order of the nodes indeed. I'm very curious about the trick, what is it?
@stakes - It is in the update I posted. Using row_number() over the shredded XML column T.N. Read more about it here.
1

This gives you the two-columns per <child> output :

DECLARE @input XML = '<child>
    <name>Fred</name>
    <sname>Flintstone</sname>
   </child>
   <child>
    <name>Bill</name>
    <sname>Gates</sname>
   </child>'

SELECT
    'name: ' + child.value('(name)[1]', 'varchar(50)'),
    'sname: ' + child.value('(sname)[1]', 'varchar(50)')
FROM @input.nodes('/child') AS nodes(child)

Output is:

name: Fred  |  sname: Flintstone
name: Bill  |  sname: Gates

If you want just one column, you can use this instead:

SELECT
    'name: ' + child.value('(name)[1]', 'varchar(50)')
FROM @input.nodes('/child') AS nodes(child)

UNION

SELECT
    'sname: ' + child.value('(sname)[1]', 'varchar(50)')
FROM @input.nodes('/child') AS nodes(child)

and this gives you this output:

(No column name)
name: Bill
name: Fred
sname: Flintstone
sname: Gates
Improve this answer

1 Comment

Thanks Marc. It is still not exactly what I want, but in the mean time I already figured it out. See my own answer... :-S

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.