May 29, 2012 at 1:40 pm
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
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
May 29, 2012 at 1:51 pm
May help if you also provide the code you have tried so far. Nothing like seeing what doesn't work to help look for what does work.
May 29, 2012 at 1:53 pm
I will try to translate my current failure into the context of this example and post it ASAP.
Thanks!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
May 29, 2012 at 2:16 pm
Okay, hopefully you can contain your laughter before you pass out from oxygen deprivation... 😉
SELECT
( SELECT
( SELECT Company_ID AS id,
Company_Name AS name,
( SELECT ( SELECT Employee_ID AS id,
Employee_Name AS name
FROM Employees
WHERE Employees.Company_ID = company.Company_ID
FOR XML AUTO, TYPE, ELEMENTS
) AS employee
FOR XML AUTO, TYPE, ELEMENTS
) AS employees
FROM Companies AS company
FOR XML AUTO, TYPE, ELEMENTS
) AS companies FOR XML AUTO, TYPE, ELEMENTS
) AS import FOR XML AUTO, TYPE, ELEMENTS;
Go ahead and say it... "WOW that's insanely wrong." I know, that's why I'm here! I tried breaking out just a small middle part of it, and got the same error:
SELECT Company_ID AS id,
Company_Name AS name,
( SELECT ( SELECT Employee_ID AS id,
Employee_Name AS name
FROM Employees
WHERE Employees.Company_ID = company.Company_ID
FOR XML AUTO, TYPE, ELEMENTS
) AS employee
FOR XML AUTO, TYPE, ELEMENTS
) AS employees
FROM Companies AS company
FOR XML AUTO, TYPE, ELEMENTS
In my research I found a few sources that said I needed to use SELECT * FROM, like so:
SELECT Company_ID AS id,
Company_Name AS name,
( SELECT * FROM ( SELECT Employee_ID AS id,
Employee_Name AS name
FROM Employees
WHERE Employees.Company_ID = company.Company_ID
FOR XML AUTO, TYPE, ELEMENTS
) AS employee
FOR XML AUTO, TYPE, ELEMENTS
) AS employees
FROM Companies AS company
FOR XML AUTO, TYPE, ELEMENTS
But then I get the error "No column was specified for column 1 of 'Employees'." Ugh. Shoulda been an accountant like my daddy told me.
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
May 29, 2012 at 2:16 pm
Part of the problem is that you are using FOR XML AUTO. It is usually much easier to construct the necessary XML structure using FOR XML PATH with nested subqueries. That being said, it is possible to do this using FOR XML AUTO.
FOR XML AUTO
SELECT Company.Company_ID, Company.Company_Name, Employee.Employee_ID, Employee.Employee_Name
FROM Companies AS Company
INNER JOIN Employees
ON Company.Company_ID = Employees.Company_ID
CROSS APPLY (
SELECT Employees.Employee_ID, Employees.Employee_Name
) Employee
FOR XML AUTO, ELEMENTS, ROOT('Companies')
FOR XML PATH
SELECT Company_ID, Company_Name, (
SELECT Employee_ID, Employee_Name
FROM Employees
WHERE Employees.Company_ID = Companies.Company_ID
FOR XML PATH('Employee'), TYPE, ROOT('Employees')
) AS [*]
FROM Companies
FOR XML PATH('Company'), ROOT('Companies')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 29, 2012 at 2:18 pm
Thank you Drew, I will start digging into this immediately.
Much obliged!
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
May 29, 2012 at 2:33 pm
Actually I realized that my FOR XML AUTO code didn't have the Employees node. Here is corrected code.
FOR XML AUTO
SELECT Company.Company_ID, Company.Company_Name, Employees.Placeholder, Employee.Employee_ID, Employee_Name
FROM Companies AS Company
CROSS APPLY (
SELECT Company.Company_ID, NULL AS Placeholder
) AS Employees
INNER JOIN dbo.Employees AS Employee
ON Employees.Company_ID = Employee.Company_ID
FOR XML AUTO, ELEMENTS, ROOT('Companies')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 29, 2012 at 2:33 pm
Wow, within minutes I'm pretty sure I'm just about there. Thank you so much for that, extremely helpful. (I went with PATH... I completely get it now.)
Thanks so much!!!
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply