FOR XML question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

FOR XML question

Author

Topic

littlewing
Starting Member

33 Posts

Posted - 2009-06-10 : 08:29:09

I have this FOR XML query returning the following from AdventureWorks:

select top 5
ContactID, Firstname, Lastname, EmailAddress
from Person.Contact
FOR XML RAW ('Row'), ROOT ('File'), ELEMENTS

returns:

<File>
<Row>
<ContactID>1</ContactID>
<Firstname>Gustavo</Firstname>
<Lastname>Achong</Lastname>
<EmailAddress>gustavo0@adventure-works.com</EmailAddress>
</Row>
<Row>
<ContactID>2</ContactID>
<Firstname>Catherine</Firstname>
<Lastname>Abel</Lastname>
<EmailAddress>catherine0@adventure-works.com</EmailAddress>
</Row>
<Row>
<ContactID>3</ContactID>
<Firstname>Kim</Firstname>
<Lastname>Abercrombie</Lastname>
<EmailAddress>kim2@adventure-works.com</EmailAddress>
</Row>
<Row>
<ContactID>4</ContactID>
<Firstname>Humberto</Firstname>
<Lastname>Acevedo</Lastname>
<EmailAddress>humberto0@adventure-works.com</EmailAddress>
</Row>
<Row>
<ContactID>5</ContactID>
<Firstname>Pilar</Firstname>
<Lastname>Ackerman</Lastname>
<EmailAddress>pilar1@adventure-works.com</EmailAddress>
</Row>
</File>

What I really would like is:

<?xml version="1.0" encoding="UTF-8"?>
<File>
<Row>
<Column Name="ContactID">1</Column>
<Column Name="Firstname">Gustavo</Column>
<Column Name="Lastname">Achong</Column>
<Column Name="EmailAddress">gustavo0@adventure-works.com</Column>
</Row>
<Row>
<Column Name="ContactID">2</Column>
<Column Name="Firstname">Catherine</Column>
<Column Name="Lastname">Abel</Column>
<Column Name="EmailAddress">catherine0@adventure-works.com</Column>
</Row>
<Row>
<Column Name="ContactID">3</Column>
<Column Name="Firstname">Kim</Column>
<Column Name="Lastname">Abercrombie</Column>
<Column Name="EmailAddress">kim2@adventure-works.com</Column>
</Row>
<Row>
<Column Name="ContactID">4</Column>
<Column Name="Firstname">Humberto</Column>
<Column Name="Lastname">Acevedo</Column>
<Column Name="EmailAddress">humberto0@adventure-works.com</Column>
</Row>
<Row>
<Column Name="ContactID">5</Column>
<Column Name="Firstname">Pilar</Column>
<Column Name="Lastname">Ackerman</Column>
<Column Name="EmailAddress">pilar1@adventure-works.com</Column>
</Row>
</File>

Can anyone help with the FOR XML clause here? Also can this be saved directly to an XML file without using SSIS?

Thank you.
LW

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-06-10 : 16:55:24

ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127403

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources