XML say wha?


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:

xml_blog.txt

 

2 Comments, Comment or Ping

  1. SR

    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

  2. admin

    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?”