How to format XML using T-SQL

Question

I've got a table called StoreFolder that contains an XML field called FolderXml. Each row contains an XML value like the following:

<Folder FolderName="test" StoreName="TestStore" />

I need to get the Folders in the following XML format:

<Folders>
  <Folder FolderName="test1" StoreName="TestStore1" />
  <Folder FolderName="test2" StoreName="TestStore1" />
  <Folder FolderName="test3" StoreName="TestStore1" />
</Folders>

I've tried using the following SQL statement:

SELECT   FolderXml
FROM     StoreFolder
FOR XML PATH(''), ROOT('Folders')

But it outputs the following XML:

<Folders>
  <FolderXml>
    <Folder FolderName="test1" StoreName="TestStore1" />
  </FolderXml>
  <FolderXml>
    <Folder FolderName="test2" StoreName="TestStore1" />
  </FolderXml>
  <FolderXml>
    <Folder FolderName="test3" StoreName="TestStore1" />
  </FolderXml>
</Folders>

Can someone please show me how to get the XML in the following format (using T-SQL):

<Folders>
  <Folder FolderName="test1" StoreName="TestStore1" />
  <Folder FolderName="test2" StoreName="TestStore1" />
  <Folder FolderName="test3" StoreName="TestStore1" />
</Folders>

Many thanks

Dominic Goulet · Accepted Answer · 2012-12-07 17:57:28Z

3

You are simply missing a convert/cast operation :

select Convert(Xml, FolderXml)
  from StoreFolder
for xml path(''), root('Folders')

Your FolderXml field is being treated as a varchar/nvarchar field, and then added inside an Xml node. Converting it to Xml beforehand ensure that it won't be wrapped in a parent tag.

edited Dec 7, 2012 at 17:57

answered Dec 7, 2012 at 14:17

Dominic Goulet

8,1437 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ruzbeh Irani · Accepted Answer · 2012-12-07 13:47:45Z

0

select FolderName as "@foldername",StoreName as "@StoreName"
FROM StoreFolder
FOR XML PATH('Folder'),ROOT('Folder')

answered Dec 7, 2012 at 13:47

Ruzbeh Irani

2,4561 gold badge19 silver badges10 bronze badges

1 Comment

m_collard Over a year ago

It doesn't work as the XML data is stored in a field called FolderXml.

Daniel PP Cabral · Accepted Answer · 2012-12-07 14:19:57Z

0

DECLARE @T TABLE(
    FolderXml XML
)

INSERT INTO @T VALUES
('<Folder FolderName="test1" StoreName="TestStore1" />'),
('<Folder FolderName="test2" StoreName="TestStore1" />'),
('<Folder FolderName="test3" StoreName="TestStore1" />')


SELECT (SELECT T.FolderXml)
FROM @T AS T
FOR XML PATH (''), ROOT('Folders')

answered Dec 7, 2012 at 14:19

Daniel PP Cabral

1,6341 gold badge13 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to format XML using T-SQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related