I am working on a project where I need to export XML from my database, in the process of asking for help I decided I would document here (a similar version of what I am working on) what I was stuck on and ask for help in my comments since twitter #sqlhelp needed more info.
I am currently trying to use FOR XML EXPLICIT to pull this data but have realized that I might be using the wrong tool. We have other feeds written in C#, I was just trying to get around the script task in SSIS.
Here you go:
Arlene Gray began her Microsoft SQL Server career in 2001 at MarketLinx Solutions (now 
2 Comments, Comment or Ping
SQL 2008:
WITH cteParent(id, id_child, id_parent, attr_1, attr_3, attr_4) AS
(
SELECT id, id_child, id_parent, attr_1, attr_3, attr_4
FROM dbo.products
WHERE id_parent = id_child
)
,cteChild(id, id_child, id_parent, attr_2, attr_3) AS
(
SELECT id, id_child, id_parent, attr_2, attr_3
FROM dbo.products
WHERE id_parent != id_child
)
SELECT
(
SELECT
cteP.id AS “id”
,cteP.attr_1 AS “parentattrib/singleattrib/attr_1″
,cteP.attr_3 AS “parentattrib/singleattrib/attr_3″
,cteP.attr_4 AS “parentattrib/singleattrib/attr_4″
,(SELECT (
SELECT
cteC.id AS “id”
,cteC.attr_2 AS “childattrib/singleattrib/attr_2″
,cteC.attr_3 AS “childattrib/singleattrib/attr_3″
FROM cteChild cteC
WHERE cteP.id_parent = cteC.id_parent
FOR XML PATH (‘child’), TYPE
)FOR XML PATH (‘childids’), TYPE, ELEMENTS XSINIL)
FROM cteParent cteP
FOR XML PATH (‘parent’), ROOT (‘item’), TYPE
)
FOR XML PATH (‘items’), ROOT (‘catalog’), TYPE
GO
April 29th, 2010
Thank you! I managed to get something very similar to work however what I found out is that this doesn’t scale. When I tried to shove half a million records into it the resulting XML doc was being held in memory and I get an error.
I need the code to write the XML doc as it generates it.
I am seeing no way around this but to use a script task (C#).
May 17th, 2010
Reply to “XML say wha?”