CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
smadirondack
smadirondack

Reputation: 77

I need to wrap a result set of xml data with a parent tag

I have a table with a column of type xml. I'd like to wrap the result set in a parent tag.

For example:

Here is the table result set:

SELECT *
FROM MyColorTable

Result :

<Color>Red</Color>
<Color>Orange</Color>
<Color>Yellow</Color>
<Color>Green</Color>
<Color>Blue</Color>
<Color>Indigo</Color>
<Color>Violet</Color>

I would like a query that will set @MyXml as below:

DECLARE @MyXml xml
SELECT @MyXml

Desired output:

<Colors>
        <Color>Red</Color>
        <Color>Orange</Color>
        <Color>Yellow</Color>
        <Color>Green</Color>
        <Color>Blue</Color>
        <Color>Indigo</Color>
        <Color>Violet</Color>
</Colors>

Thanks

Upvotes: 3

Views: 1395

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

1) Updated solution:

DECLARE @MyTable TABLE (Col1 XML);
INSERT @MyTable VALUES (N'<Color>Red</Color>');
INSERT @MyTable VALUES (N'<Color>Orange</Color>');
INSERT @MyTable VALUES (N'<Color>Yellow</Color>');
INSERT @MyTable VALUES (N'<Color>Blue</Color>');
INSERT @MyTable VALUES (N'<Color>Green</Color>');
INSERT @MyTable VALUES (N'<Color>Indigo</Color>');
INSERT @MyTable VALUES (N'<Color>Violet</Color>');

SELECT  t.Col1 AS '*'
FROM    @MyTable t
FOR XML PATH(''), ROOT('Colors');

Output:

<Colors>
  <Color>Red</Color>
  <Color>Orange</Color>
  <Color>Yellow</Color>
  <Color>Blue</Color>
  <Color>Green</Color>
  <Color>Indigo</Color>
  <Color>Violet</Color>
</Colors>

2) If you want to change just the content of @MyXML variable then one solution is to use query method thus:

DECLARE @MyXml XML;

SET @MyXML = N'
<Color>Red</Color>
<Color>Orange</Color>
<Color>Yellow</Color>
<Color>Blue</Color>
<Color>Green</Color>
<Color>Indigo</Color>
<Color>Violet</Color>';

SELECT @MyXml.query('<Colors>{.}</Colors>');

Upvotes: 3

gotqn
gotqn

Reputation: 43636

The SQL Server has a good build-in support for work with XML. There is no need to store a XML tag in your column.

You should remove the unnecessary <Color> tag in order to optimize the work with XML and reduce the column storage - it is easy to generate it when it is needed like this:

SELECT [color]
FROM DataSource FOR XML PATH('Color'), ROOT('Colors')

In you are not allowed to do this, you can remove the tags with SQL like this:

CREATE TABLE DataSource
(
  [color] VARCHAR(32) 
)

INSERT INTO DataSource ([color])
VALUES ('<Color>Red</Color>')
      ,('<Color>Orange</Color>')
      ,('<Color>Yellow</Color>')
      ,('<Color>Blue</Color>')
      ,('<Color>Green</Color>')
      ,('<Color>Indigo</Color>')
      ,('<Color>Violet</Color>')

SELECT REPLACE(REPLACE([color],'<Color>',''),'</Color>','')
FROM DataSource FOR XML PATH('Color'), ROOT('Colors')

But the above seems very wrong for me and I will advise you to change the way you are storing the values in the table.

Upvotes: 1

code save
code save

Reputation: 1106

Please Try it, I hope it will work

select color from MyColorTable for xml path('Colors')

or

select columnname as 'color' from MyColorTable for xml path('Colors')

Upvotes: 0

Related Questions