CODEWITHSUNDEEP
sql-serverxml
user2896152
user2896152

Reputation: 816

Get an XML of a Table that contains XMLs

I've a table @t_myTable that contains XMLs like this

------
1. <xml><.....></xml>
2. <xml><.....></xml>
3. <xml><.....></xml>
------

Now, what I want is having a single XML containing all those XMLs. I tried to solve this problem with this query

SELECT myTable.Value
FROM @t_myTable myTable
FOR XML AUTO, ROOT ('XML'))

but what I get is an XML with a lot of nested nodes, such us

<XML>                  <- the XML root as I want
  <myTable>            <- the name of the Table, useless in my XML
    <Value>            <- the name of the column, useless in my XML
      <xml>            <- the xml that I want to append to ROOT Node
        ....           <- ecc. ecc.

So what I want to ask is how can I do to minimaze the nodes in xml in a simplest way like this:

<XML>
  <xml>
  .......
  </xml>
  <xml>
  .......
  </xml>
<XML>

??? Thank you in advance for your support

Upvotes: 0

Views: 22

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this

DECLARE @tbl TABLE(xml XML);
INSERT INTO @tbl VALUES
 ('<xml><a>Some a value</a></xml>')
,('<xml><a>Another a value</a></xml>')
,('<xml><a>And one more a value</a></xml>');

SELECT xml AS [*]
FROM  @tbl
FOR XML PATH(''),ROOT('xml')

The result

<xml>
  <xml>
    <a>Some a value</a>
  </xml>
  <xml>
    <a>Another a value</a>
  </xml>
  <xml>
    <a>And one more a value</a>
  </xml>
</xml>

Upvotes: 3

Related Questions