locked
How to generate XML using Recursion and FOR XML, without using recursive Function Call RRS feed

  • Question

  • Hi,
    I am trying to generate XML Tree structure , I am using single table which points to it self as foreign key relationship.
     
    -- My Data
    CREATE TABLE PARTS(id int, parent int, name nvarchar(500))
    GO
    INSERT INTO PARTS 
      SELECT 1, NULL, N'car'
      UNION
      SELECT 2, 1, N'engine'
      UNION
      SELECT 3, 1, N'body'
      UNION
      SELECT 4, 3, N'door'
      UNION
      SELECT 5, 3, N'fender'
      UNION
      SELECT 6, 4, N'window'
      UNION
      SELECT 7, 2, N'piston'
     

    -- Expected Output in XML using FOR XML Recursive query

     

    <Parts id="1" name="car">

    <Parts id="2" name="engine">

    <Parts id="3" name="body">

    <Parts id="4" name="door">

    <Parts id="6" name="window" />

    </Parts>

    <Parts id="5" name="fender" />

    </Parts>

    </Parts>

    </Parts>

     
    I got solution from net but it decrease performance of my execution procedure 
    --Please refer bellow script I got as solution from net
    CREATE FUNCTION PartsList(@PartsNo int)
    RETURNS XML
    WITH RETURNS NULL ON NULL INPUT 
    BEGIN RETURN 
      (SELECT id as "@id", name as "@name", 
          CASE WHEN parent=@PartsNo
          THEN dbo.PartsList(id)
          END
       FROM dbo.PARTS WHERE parent=@PartsNo
       FOR XML PATH('Parts'), TYPE)
    END
    select id as "@id", name as "@name", 
           CASE WHEN id=3
           THEN dbo.PartsList(id)
           END
    FROM PARTS
    WHERE id=3
    FOR XML PATH('Parts'), TYPE
    
    Is there any way to generate Tree structure of XML data by firing recursive query instead of Recursive function call?
     
    Thursday, November 29, 2007 12:04 PM

Answers

  • Purvesh,

    The query given below uses a recursive CTE and uses FOR XML EXPLICIT to generate the XML tree as:

    <Part id="1" name="car">

    <Part id="2" name="engine">

    <Part id="7" name="piston" />

    </Part>

    <Part id="3" name="body">

    <Part id="4" name="door">

    <Part id="6" name="window" />

    </Part>

    <Part id="5" name="fender" />

    </Part>

    </Part>

     

    This hierarchy is based on the sample data you provided. Note that the EXPECTED OUTPUT you specified is wrong "Body" is not a child element of "Engine". The correct tree based on the sample data is given above.

     

    Here is the recursive query that returns this result.

     

    ;WITH Parts1

    AS

    (

    SELECT

    0 AS [Level],

    [id],

    Parent,

    [name],

    CAST( [id] AS VARBINARY(MAX)) AS Sort

    FROM parts

    WHERE Parent IS NULL

    UNION ALL

    SELECT

    [Level] + 1,

    p.[id],

    p.Parent,

    p.[name],

    CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))

    FROM parts p

    INNER JOIN Parts1 c ON p.parent = c.id

    ),

    Parts2 AS (

    SELECT

    [Level] + 1 AS Tag,

    [id],

    Parent,

    [name],

    sort

    FROM Parts1

    ),

    Parts3 AS (

    SELECT

    *,

    (SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag

    FROM Parts2 r1

    )

    SELECT

    Tag,

    ParentTag as Parent,

    CASE WHEN tag = 1 THEN [id] ELSE NULL END AS 'Part!1!id', CASE WHEN tag = 1 THEN [name] ELSE NULL END AS 'Part!1!name',

    CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'Part!2!id',CASE WHEN tag = 2 THEN [name] ELSE NULL END AS 'Part!2!name',

    CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'Part!3!id',CASE WHEN tag = 3 THEN [name] ELSE NULL END AS 'Part!3!name',

    CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'Part!4!id',CASE WHEN tag = 4 THEN [name] ELSE NULL END AS 'Part!4!name',

    CASE WHEN tag = 5 THEN [id] ELSE NULL END AS 'Part!5!id',CASE WHEN tag = 5 THEN [name] ELSE NULL END AS 'Part!5!name'

    FROM Parts3

    ORDER BY sort

    FOR XML EXPLICIT

     

    Note that this query is limited to the depth of 5 levels. But you can extend it to any number of levels by following the method I used above.

     

    Friday, November 30, 2007 12:56 PM
  • Hi once again,

    The following is implementation of my thoughts.

    Step 1. Creating the CLR function AsXmlTree. This function transforms input raw XML and returns a desired XML tree.

    Code Block

    using System;

    using System.Text;

    using System.Xml;

    using System.Xml.Xsl;

    using System.IO;

    using Microsoft.SqlServer.Server;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

     

    public class Functions

    {

    [SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)]

    public static SqlXml AsXmlTree (

    SqlXml inXml,

    SqlString idCol,

    SqlString parIdCol )

    {

    using (SqlConnection connection = new SqlConnection("context connection=true"))

    {

    connection.Open();

     

    XslCompiledTransform xslt = GetXSLT(idCol.ToString(), parIdCol.ToString());

    MemoryStream outStream = new MemoryStream();

    XmlReader inReader = inXml.CreateReader();

    xslt.Transform(inReader, null, outStream);

    outStream.Position = 0;

    inReader.Close();

     

    return ( new SqlXml(outStream) );

    }

    }

    private static XslCompiledTransform GetXSLT(string idCol, string parIdCol)

    {

    StringBuilder sb = new StringBuilder();

     

    sb.Append("<?xml version='1.0' encoding='utf-8'?>");

    sb.Append("<xsl:transform xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0'>");

    sb.Append("<xsl:output method='xml' version='1.0' encoding='utf-8' omit-xml-declaration='yes'/>");

    sb.Append("<xsl:key name='children' match='*' use='@" + parIdCol + "'/>");

    sb.Append("<xsl:template match='/'>");

    sb.Append("  <xsl:apply-templates select='*[not(@" + parIdCol + ")]'/>");

    sb.Append("</xsl:template>");

    sb.Append("<xsl:template match='*'>");

    sb.Append("  <xsl:element name='{name()}'>");

    // if you want to obtain xml without parent attribute, uncomment the next line and comment the next next line :)

    // sb.Append(" <xsl:for-each select='@*[name()!=\"" + parIdCol + "\"]'><xsl:copy/></xsl:for-each>");

    sb.Append("    <xsl:for-each select='@*'><xsl:copy/></xsl:for-each>");

    sb.Append("    <xsl:apply-templates select='key(\"children\",@" + idCol + ")'/>");

    sb.Append("  </xsl:element>");

    sb.Append("</xsl:template>");

    sb.Append("</xsl:transform>");

     

    XmlReader xslReader = XmlReader.Create(new StringReader(sb.ToString()));

    XslCompiledTransform xslt = new XslCompiledTransform();

    xslt.Load(xslReader);

    xslReader.Close();

     

    return xslt;

    }

    }

     

     

     

     

     

    Step 2. Compiling the above code into assembly (for example, into SqlXmlRoutine.dll).

     

     

     

    Step 3. Registration the assembly and function in database.

     

    Code Block

    -- if CLR is disabled:

    -- sp_configure 'clr enabled', 1
    -- GO
    -- RECONFIGURE


    GO

    CREATE ASSEMBLY SqlXmlRoutine FROM 'SqlXmlRoutine.dll'  -- full path to assembly

     

    GO

    CREATE FUNCTION dbo.ForXmlTree
    (
      @inXml xml,
      @idCol nvarchar(32),
      @parIdCol nvarchar(32)
    )
    RETURNS xml
    AS EXTERNAL NAME SqlXmlRoutine.Functions.AsXmlTree


     

     

     

     

    Step 4. Use the function:

     

    SELECT dbo.ForXmlTree
    (
      (
        SELECT *
           FROM PARTS
        FOR XML RAW ('Parts')      -- without a root element!
      ),

      'id', 'parent'
    )

     

     

    Regards,

    Lev

    Sunday, December 2, 2007 5:42 AM

All replies

  • Purvesh,

    The query given below uses a recursive CTE and uses FOR XML EXPLICIT to generate the XML tree as:

    <Part id="1" name="car">

    <Part id="2" name="engine">

    <Part id="7" name="piston" />

    </Part>

    <Part id="3" name="body">

    <Part id="4" name="door">

    <Part id="6" name="window" />

    </Part>

    <Part id="5" name="fender" />

    </Part>

    </Part>

     

    This hierarchy is based on the sample data you provided. Note that the EXPECTED OUTPUT you specified is wrong "Body" is not a child element of "Engine". The correct tree based on the sample data is given above.

     

    Here is the recursive query that returns this result.

     

    ;WITH Parts1

    AS

    (

    SELECT

    0 AS [Level],

    [id],

    Parent,

    [name],

    CAST( [id] AS VARBINARY(MAX)) AS Sort

    FROM parts

    WHERE Parent IS NULL

    UNION ALL

    SELECT

    [Level] + 1,

    p.[id],

    p.Parent,

    p.[name],

    CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))

    FROM parts p

    INNER JOIN Parts1 c ON p.parent = c.id

    ),

    Parts2 AS (

    SELECT

    [Level] + 1 AS Tag,

    [id],

    Parent,

    [name],

    sort

    FROM Parts1

    ),

    Parts3 AS (

    SELECT

    *,

    (SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag

    FROM Parts2 r1

    )

    SELECT

    Tag,

    ParentTag as Parent,

    CASE WHEN tag = 1 THEN [id] ELSE NULL END AS 'Part!1!id', CASE WHEN tag = 1 THEN [name] ELSE NULL END AS 'Part!1!name',

    CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'Part!2!id',CASE WHEN tag = 2 THEN [name] ELSE NULL END AS 'Part!2!name',

    CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'Part!3!id',CASE WHEN tag = 3 THEN [name] ELSE NULL END AS 'Part!3!name',

    CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'Part!4!id',CASE WHEN tag = 4 THEN [name] ELSE NULL END AS 'Part!4!name',

    CASE WHEN tag = 5 THEN [id] ELSE NULL END AS 'Part!5!id',CASE WHEN tag = 5 THEN [name] ELSE NULL END AS 'Part!5!name'

    FROM Parts3

    ORDER BY sort

    FOR XML EXPLICIT

     

    Note that this query is limited to the depth of 5 levels. But you can extend it to any number of levels by following the method I used above.

     

    Friday, November 30, 2007 12:56 PM
  • Purvesh,

    The query given below uses a recursive CTE and uses FOR XML EXPLICIT to generate the XML tree as:

    <Part id="1" name="car">

    <Part id="2" name="engine">

    <Part id="7" name="piston" />

    </Part>

    <Part id="3" name="body">

    <Part id="4" name="door">

    <Part id="6" name="window" />

    </Part>

    <Part id="5" name="fender" />

    </Part>

    </Part>

     

    This hierarchy is based on the sample data you provided. Note that the EXPECTED OUTPUT you specified is wrong "Body" is not a child element of "Engine". The correct tree based on the sample data is given above.

     

    Here is the recursive query that returns this result.

     

    ;WITH Parts1

    AS

    (

    SELECT

    0 AS [Level],

    [id],

    Parent,

    [name],

    CAST( [id] AS VARBINARY(MAX)) AS Sort

    FROM parts

    WHERE Parent IS NULL

    UNION ALL

    SELECT

    [Level] + 1,

    p.[id],

    p.Parent,

    p.[name],

    CAST( SORT + CAST(p.[id] AS BINARY(4)) AS VARBINARY(MAX))

    FROM parts p

    INNER JOIN Parts1 c ON p.parent = c.id

    ),

    Parts2 AS (

    SELECT

    [Level] + 1 AS Tag,

    [id],

    Parent,

    [name],

    sort

    FROM Parts1

    ),

    Parts3 AS (

    SELECT

    *,

    (SELECT Tag FROM Parts2 r2 WHERE r2.ID = r1.parent) AS ParentTag

    FROM Parts2 r1

    )

    SELECT

    Tag,

    ParentTag as Parent,

    CASE WHEN tag = 1 THEN [id] ELSE NULL END AS 'Part!1!id', CASE WHEN tag = 1 THEN [name] ELSE NULL END AS 'Part!1!name',

    CASE WHEN tag = 2 THEN [id] ELSE NULL END AS 'Part!2!id',CASE WHEN tag = 2 THEN [name] ELSE NULL END AS 'Part!2!name',

    CASE WHEN tag = 3 THEN [id] ELSE NULL END AS 'Part!3!id',CASE WHEN tag = 3 THEN [name] ELSE NULL END AS 'Part!3!name',

    CASE WHEN tag = 4 THEN [id] ELSE NULL END AS 'Part!4!id',CASE WHEN tag = 4 THEN [name] ELSE NULL END AS 'Part!4!name',

    CASE WHEN tag = 5 THEN [id] ELSE NULL END AS 'Part!5!id',CASE WHEN tag = 5 THEN [name] ELSE NULL END AS 'Part!5!name'

    FROM Parts3

    ORDER BY sort

    FOR XML EXPLICIT

     

    Note that this query is limited to the depth of 5 levels. But you can extend it to any number of levels by following the method I used above.

    Friday, November 30, 2007 12:56 PM
  • Hi Jacob,

     

    Thanks for reply, Sorry for I have given wrong XML as expected output but my question is that How to get hierarchy for N Level, If you have any idea please suggest me , because of as per my requirement I am searching for N Level of hierarchy , which meet by my code I have uploaded by recursive function call but due to such a bulk data in my Database table its becoming much time consuming process so I was looking for Recursive Query to refer N Level hierarchy. Please suggest me if you have any Idea.

    Friday, November 30, 2007 1:18 PM
  • Hi Purvesh,

    If you really need an N level, then you might need a recursive function. But if you want to limit the levels say 10, 50 or 100, you can use the approach I presented above.

     

    I have never came across such a requirement. I started experimenting with this after I read your post. And what I have given above is the best I could come up with :-).

     

    Jacob

    Friday, November 30, 2007 1:54 PM
  • Thanks Jacob,

     

    I have tested it with 15 level n its working fine, but if you found any specific solution to solve this problem guide me. because I will pass this XML to my treeView control to display data in hierarchy.

    Friday, November 30, 2007 2:03 PM
  • No prob purvesh.

    probably you can keep a watch on my blog: http://jacobsebastian.blogspot.com. that is where i put all the interesting stuff I find.

     

     

    Friday, November 30, 2007 2:59 PM
  • Hi,


    In those cases, I'm using XSL:

     

    SELECT * FROM PARTS FOR XML RAW --> XSLT --> A desired XML

     

    It is very simply and efficiently (if N is big)...

     

    Saturday, December 1, 2007 1:11 AM
  • Hi once again,

    The following is implementation of my thoughts.

    Step 1. Creating the CLR function AsXmlTree. This function transforms input raw XML and returns a desired XML tree.

    Code Block

    using System;

    using System.Text;

    using System.Xml;

    using System.Xml.Xsl;

    using System.IO;

    using Microsoft.SqlServer.Server;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

     

    public class Functions

    {

    [SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)]

    public static SqlXml AsXmlTree (

    SqlXml inXml,

    SqlString idCol,

    SqlString parIdCol )

    {

    using (SqlConnection connection = new SqlConnection("context connection=true"))

    {

    connection.Open();

     

    XslCompiledTransform xslt = GetXSLT(idCol.ToString(), parIdCol.ToString());

    MemoryStream outStream = new MemoryStream();

    XmlReader inReader = inXml.CreateReader();

    xslt.Transform(inReader, null, outStream);

    outStream.Position = 0;

    inReader.Close();

     

    return ( new SqlXml(outStream) );

    }

    }

    private static XslCompiledTransform GetXSLT(string idCol, string parIdCol)

    {

    StringBuilder sb = new StringBuilder();

     

    sb.Append("<?xml version='1.0' encoding='utf-8'?>");

    sb.Append("<xsl:transform xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0'>");

    sb.Append("<xsl:output method='xml' version='1.0' encoding='utf-8' omit-xml-declaration='yes'/>");

    sb.Append("<xsl:key name='children' match='*' use='@" + parIdCol + "'/>");

    sb.Append("<xsl:template match='/'>");

    sb.Append("  <xsl:apply-templates select='*[not(@" + parIdCol + ")]'/>");

    sb.Append("</xsl:template>");

    sb.Append("<xsl:template match='*'>");

    sb.Append("  <xsl:element name='{name()}'>");

    // if you want to obtain xml without parent attribute, uncomment the next line and comment the next next line :)

    // sb.Append(" <xsl:for-each select='@*[name()!=\"" + parIdCol + "\"]'><xsl:copy/></xsl:for-each>");

    sb.Append("    <xsl:for-each select='@*'><xsl:copy/></xsl:for-each>");

    sb.Append("    <xsl:apply-templates select='key(\"children\",@" + idCol + ")'/>");

    sb.Append("  </xsl:element>");

    sb.Append("</xsl:template>");

    sb.Append("</xsl:transform>");

     

    XmlReader xslReader = XmlReader.Create(new StringReader(sb.ToString()));

    XslCompiledTransform xslt = new XslCompiledTransform();

    xslt.Load(xslReader);

    xslReader.Close();

     

    return xslt;

    }

    }

     

     

     

     

     

    Step 2. Compiling the above code into assembly (for example, into SqlXmlRoutine.dll).

     

     

     

    Step 3. Registration the assembly and function in database.

     

    Code Block

    -- if CLR is disabled:

    -- sp_configure 'clr enabled', 1
    -- GO
    -- RECONFIGURE


    GO

    CREATE ASSEMBLY SqlXmlRoutine FROM 'SqlXmlRoutine.dll'  -- full path to assembly

     

    GO

    CREATE FUNCTION dbo.ForXmlTree
    (
      @inXml xml,
      @idCol nvarchar(32),
      @parIdCol nvarchar(32)
    )
    RETURNS xml
    AS EXTERNAL NAME SqlXmlRoutine.Functions.AsXmlTree


     

     

     

     

    Step 4. Use the function:

     

    SELECT dbo.ForXmlTree
    (
      (
        SELECT *
           FROM PARTS
        FOR XML RAW ('Parts')      -- without a root element!
      ),

      'id', 'parent'
    )

     

     

    Regards,

    Lev

    Sunday, December 2, 2007 5:42 AM
  • You are te fkn boos sll, that post helped me sooooo much, thanks.
    Friday, February 29, 2008 9:55 PM