Tranform multiple data rows into an xml string

Question

Tranform multiple data rows into an xml string

EJ-128112

Valued Member

Points: 72
More actions
June 29, 2007 at 1:18 pm

#124519

I have data in a table that I would like to convert into an XML string representation without using cursors.
Let me give an example of the table with 2 rows of data:
ElementsTable
GroupingID                ElementName                   ElementValue
1                             Name1                            Value1
1                             Name2                            Value2

I need the output of a stored procedure to generate this:
<Name1>Value1</Name1><Name2>Value2</Name2>
Granted I could, do a select from the table with a cursor and take each row, manipulate it to add the XML tags, and keep appending it to a @Result variable but I want to do this without a cursor, I need this to be highly efficient. I tried using some of the FOR XML options but they did not seem to work because they generate the XML elements with the table column names (ElementName and ElementValue). We actually have the element name/value pairs stored as row data. Any help would be appreciated. Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yeah this isn't all that easy...adn you can get bitten by the varchar 8000 length issue if the data you are combining gets much over 4000 chars...varchar(8000) + varchar(8000) ends up getting ugly.

here's an example with a variable...you could use a function as well.

SET NOCOUNT ON

CREATE TABLE ElementsTable(GroupingID int,ElementName varchar(30),ElementValue varchar(30))

Insert into ElementsTable(GroupingID,ElementName,ElementValue) values(1,'Name1','Value1')

Insert into ElementsTable(GroupingID,ElementName,ElementValue) values(1,'Name2','Value2')

Insert into ElementsTable(GroupingID,ElementName,ElementValue) values(1,'Name3','Value3')

Insert into ElementsTable(GroupingID,ElementName,ElementValue) values(2,'Name1','Value1')

Insert into ElementsTable(GroupingID,ElementName,ElementValue) values(2,'Name2','Value2')

Insert into ElementsTable(GroupingID,ElementName,ElementValue) values(2,'Name3','Value3')

declare @varstring varchar(8000)

SET @varstring=''

Select @varstring = @varstring + '<' + ElementName + '>' + ElementValue +'</' + ElementName + '>'

from ElementsTable where groupingId=1

SELECT @varstring

Results:

<Name1>Value1</Name1><Name2>Value2</Name2><Name3>Value3</Name3>

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

EJ-128112 Valued Member Points: 72 More actions · Answer 2

Thanks. I do have a question though. Does SQL Server implicitly use a cursor when it assigns a multirow resultset to a varchar?

For instance:

Select '<' + ElementName + '>' + ElementValue +'</' + ElementName + '>'

from ElementsTable where groupingId=1

produces a three row resultset, but when you assign it to a varchar the rows become 1.

bledu SSChampion Points: 12335 More actions · Answer 3

if i got you right you can use the FOR XML AUTO

declare

@STR as nvarchar(4000)

set

@STR = (select ElementName , ElementValue

from

ElementsTable

for

xml auto)

select

@STR as xmlstr

Everything you can imagine is real.