CODEWITHSUNDEEP
sql-serverxmlqxmlquery
inon
inon

Reputation: 1772

sql server - xml query - concat - adding space

I want to take a xml string and convert it to sql-table semicolon-delimitate

declare @x xml --for example
set @x = '<root><row><a>a1</a><b>b2</b></row><row><a>aa1</a><b>bb2</b></row></root>'
SELECT T.c.query('.').query('for $i in row/*/text() return concat($i,";")').value('.', 'nvarchar(max)') AS result  
FROM   @x.nodes('root/row') T(c)

The Result:

a1; b2;
aa1; bb2;

The Result I want: (no space)

a1;b2;
aa1;bb2;

>> Bonus: Remove last semicolon

a1;b2
aa1;bb2

Thank You!

Upvotes: 0

Views: 1142

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

This can be done much easier:

DECLARE @xml XML='<root>
  <row>
    <a>a1</a>
    <b>b2</b>
  </row>
  <row>
    <a>aa1</a>
    <b>bb2</b>
  </row>
</root>';

SELECT (
      STUFF(
      (
          SELECT ';' + v.value('.','nvarchar(max)')
          FROM r.nodes('*') AS B(v)
          FOR XML PATH('')
      ),1,1,'')
      )
FROM @xml.nodes('/root/row') AS A(r)

Upvotes: 1

inon
inon

Reputation: 1772

declare @x xml --for example
set @x = '<root><row><a>a1</a><b>b2</b></row><row><a>aa1</a><b>bb2</b></row></root>'
SELECT reverse(stuff(reverse(replace(T.c.query('.').query('for $i in row/*/text() return concat($i,";")').value('.', 'nvarchar(max)'), '; ', ';')), 1, 1, '')) AS result  
FROM   @x.nodes('root/row') T(c)

Upvotes: 0

TTeeple
TTeeple

Reputation: 2989

A combination of REPLACE, REVERSE, and STUFF is how i was able to achieve this:

declare @x xml --for example
set @x = '<root><row><a>a1</a><b>b2</b></row><row><a>aa1</a><b>bb2</b></row></root>'
SELECT reverse(stuff(reverse(replace(T.c.query('.').query('for $i in row/*/text() return concat($i,";")').value('.', 'nvarchar(max)'), ' ', '')), 1, 1, '')) AS result  
FROM   @x.nodes('root/row') T(c)

There might be a better way to get rid of the spaces since this would also replace any spaces in the XML node values as well.

Upvotes: 1

Related Questions