How to returns the text() of an xml value with a space between the nodes value?

Question

In SQL Server 2008 R2,

Considering the following xml

DECLARE @xml xml = '<data><fr>Chambre standard</fr><en>Standard room</en></data>';

How can I return the following string:

Chambre standard Standard room

Currently, I'm only able to return the string concatenated together without any space by using

SELECT @xml.query('//*/text()').value('.', 'varchar(max)')

Which return

Chambre standardStandard room

But I need to insert a space in there.

How could I insert a space between the node text?

Will the element have additional elements besides just and ? — 8kb, Aug 31 '11 at 02:59
There can actualy be more than just fr and en (more language), but your solution could work for the time being. — Pierre-Alain Vigeant, Aug 31 '11 at 12:47
I've modified the code with what I think is an improved temporary solution. — 8kb, Aug 31 '11 at 13:38

score 2 · Accepted Answer · answered Aug 31 '11 at 14:33

You can use .nodes() to shred your XML.

select T.N.value('local-name(.)', 'varchar(128)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @xml.nodes('/data/*') as T(N);

Result:

Name  Value
fr    Chambre standard
en    Standard room

Then you can use for xml path('') to bring it back together.

select stuff((select ' '+T.N.value('.', 'varchar(max)')
              from @xml.nodes('/data/*') as T(N)
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '')

Result:

(No column name)
Chambre standard Standard room

Is there any documentation online that explains "local-name" in greater detail? — 8kb, Aug 31 '11 at 15:34

How to returns the text() of an xml value with a space between the nodes value?

1 Answers1