CODEWITHSUNDEEP
sqlxmlt-sqlnodes
user2307458
user2307458

Reputation: 3

Convert multiples xml nodes data into varchar

I want to convert an xml string like this : 

'<orga_label>ORG1</orga_label><orga_label>ORG2</orga_label><orga_label>ORG3</orga_label>'

into a varchar like this : 

'ORG1, ORG2, ORG3'

in t-sql in one query.

Is that possible?

Upvotes: 0

Views: 147

Answers (3)

gbn
gbn

Reputation: 432271

You can keep is very simple and avoid XML methods here...

DECLARE @foo xml = '<orga_label>ORG1</orga_label><orga_label>ORG2</orga_label><orga_label>ORG3</orga_label>';

SELECT
    REPLACE(
      REPLACE(
        REPLACE(
          CONVERT(nvarchar(4000), @foo), '</orga_label><orga_label>', ', '
        ), 
        '<orga_label>', ''
      ),
      '</orga_label>', ''
    );

Edit: this has the advantage of not invoking the XML methods and processor.

Upvotes: 1

Alexey A.
Alexey A.

Reputation: 902

declare @xml xml = '<orga_label>ORG1</orga_label><orga_label>ORG2</orga_label><orga_label>ORG3</orga_label>';

select stuff((select 
    ',' + s from (
    select 
    a.b.value('(.)[1]', 'varchar(50)') s
    from @xml.nodes('/orga_label') a(b)
) t
for xml path('')
),1,1,'');

Upvotes: 0

mewosic
mewosic

Reputation: 485

It's better you use a xml parser in script language like ruby .

require 'rexml/document'
xml =REXML::Document.new(File.open"filename/filename.XML")
xml.each_element('//(your element)') do |sdobi|
   puts sdobi.attributes["orga_label"]
end

If you really want to use sql, it's a little bit comeplex:

SELECT SUBSTRING( columnname, LOCATE( '<orga_label',columnname ) +12, LOCATE( '</', tablename) )  from tablename

the if the substring not right try to change the number

Upvotes: 0

Related Questions