Frustration with FOR XML - 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 2005 Forums

Transact-SQL (2005)

Frustration with FOR XML

Author

Topic

ron2112
Starting Member

44 Posts

Posted - 2012-05-29 : 15:43:09

I'm reaching the end of my rope with this project, I'm really hoping someone can help me. I'm tasked with outputting a recordset to a given XML format. I've done so much reading on the topic, my head is spinning. And still nothing is working. If I give you a simple example, hopefully one of you can steer me in the right direction.

CREATE TABLE Companies
(   Company_ID int,
    Company_Name varchar(10));

CREATE TABLE Employees
(   Company_ID int,
    Employee_ID int,
    Employee_Name varchar(20));

INSERT INTO Companies
SELECT 1, 'Acme'
UNION ALL
SELECT 2, 'SmithCo';

INSERT INTO Employees
SELECT 1, 1, 'Joe'
UNION ALL
SELECT 1, 2, 'Carol'
UNION ALL
SELECT 2, 1, 'Dave'
UNION ALL
SELECT 2, 2, 'Betty';

The output you're expected to provide looks like this:

<import xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" type="companies">
<companies>
   <company>
      <id>1</id>
      <name>Acme</name>
      <employees>
         <employee>
            <id>1</id>
            <name>Joe</name>
         </employee>
         <employee>
            <id>2</id>
            <name>Carol</name>
         </employee>
      </employees>
   </company>
   <company>
      <id>2</id>
      <name>SmithCo</name>
      <employees>
         <employee>
            <id>1</id>
            <name>Dave</name>
         </employee>
         <employee>
            <id>2</id>
            <name>Betty</name>
         </employee>
      </employees>
   </company>
</companies>
</import>

I've gotten so close with this, but at the moment I'm stuck on a "FOR XML AUTO requires at least one table for generating XML tags" error. If someone could show me how they'd approach this simple example, I'm sure I could work out what I'm doing wrong.

Thank you!!!
Ron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-29 : 15:46:50

Show us the query that generated the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 15:53:19

i think this should do trick for you as per posted tables


SELECT Company_ID,Company_Name,
(SELECT    Employee_ID,
    Employee_Name
FROM Employees
WHERE Company_ID = c.Company_ID
FOR XML PATH('Employee'),Root('Employees'),type)
FROM Companies c
FOR XML PATH('company'),ROOT('Companies')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ron2112
Starting Member

44 Posts

Posted - 2012-05-30 : 07:34:58

Thank you visakh16, that helped tremendously.

Ron

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:42:53

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources