none
Sql Query to create nested XML Sitemap RRS feed

  • Question

  •  

    Hi Everyone,

     

    I am trying to create an XML sitemap file by running a sql query against a SiteMap Database.

     

    I am successful in creating the SIteMap file uptil one level deep but when the dpeth incereases the results go weird. Can someone explain where I am going wrong in the following code:

     

    Database query to built SiteMap:

     

    Code Snippet

    CREATE TABLE SiteMap (ID as Int, Title as nchar(512) , Description as nchar(512), Url as nchar(512), Roles as nchar(512), Parent as Int)

     

     

    Have filled the table with some data where the Parent is the ID of the parent node, for root it is NULL

     

    Now following is the query what I have written and performs correct with a depth of one, though trying to achieve a recursive depth nested XML

     

    Code Snippet

    CREATE VIEW test1 AS Select ID, Title, Description, Url, Roles from SiteMap

     

    CREATE VIEW test2 AS Select ID, Parent from SiteMap

     

    Select

    test2.Parent [ID],

    test1.ID ,

    test1.Title,

    test1.Description,

    test1.Url,

    test1.Roles

    FROM test2 RIGHT OUTER JOIN test1

    on test2.ID = test2.parent and test2.ID = test1.ID or test2.ID = test1.ID

    FOR XML AUTO

     

     

    Any help will be appreciated!!!!

     

     

    Wednesday, July 18, 2007 2:07 PM

Answers

  • As I have said before, I am somewhat new to using XML; I really do not know what is the best way to do what you are asking for.  Maybe Martin can show something better.  Anyway, here is what I came up with using TSQL and brute force:

     

    Code Snippet

    declare @anXmlString varchar(max)
    set @anXmlString = ''

     

    ;with nestedSiteMap as
    ( select 1 as Level,
             ID,
             Title,
             Description,
             Url,
             Roles,
             cast(str(id, 11) as varchar(121))
                 as path
        from siteMap
      where parent is null
      union all
      select level + 1,
             b.ID,
             b.Title,
             b.description,
             b.url,
             b.roles,
             cast( a.path + '/' +
                       str(b.id, 11) as varchar(121)
                 )
        from nestedSiteMap a
        join siteMap b
          on a.ID = b.parent
         and a.level < 3
    ), seqSiteMap as
    ( select row_number() over (order by path) as Seq,
             Level,
             ID,
             Title,
             Description,
             Url,
             roles
        from nestedSiteMap
    ), maxSeq as
    ( select max(seq) as maxSeq from seqSiteMap
    ), nextSite as
    ( select -1 + row_number() over(order by path)as Seq,
             Level
        from nestedSiteMap
      union all
      select seq,
             1
        from seqSiteMap a
        join maxSeq b
          on seq = maxSeq
    ), siteOutput as
    ( select a.Seq as Seq1,
             1 as Seq2,
             a.Level,
             b.Level as nextLevel,
             a.ID,
             a.Title,
             a.Description,
             a.Url,
             a.Roles
        from seqSiteMap a
        join nextSite b
          on a.seq = b.seq
      union all
      select Seq1,
             Seq2 + 1,
             Level - 1,
             nextLevel,
             ID,
             Title,
             Description,
             Url,
             Roles
        from siteOutput
      where Level > nextLevel
    )
    select @anXmlString = @anXmlString
                        + xmlLine + char(13)
    from
    (
      select seq1, seq2,
             case when level = 1  and  seq2 = 1
                  then '<test1="' + cast(id as varchar(11))
                         + '">' +char(13)
                  else '' end +
             case
               when seq2 = 1
               then replicate(' ', 2*level) + '<test2="'
                        + cast(id as varchar(11)) + '" Title="' +
                    rtrim(title) + '" Description="' +
                    rtrim(description) + '" Url="' +
                    rtrim(url) + '" Roles="' +
                    rtrim(Roles) + '"' +
                    case when nextLevel <= Level
                         then ' />'
                         else ' >'
                    end
               else '' end +
             case when seq2 > 1 and level = 1
                  then '  </test2>' + char(13) + '</test1>'
                  when seq2 > 1 and level > 1
                  then replicate(' ', 2*level) + '</test2>'
                  else ''
             end as xmlLine
        from siteOutput
    ) z
    order by seq1, seq2

     

    select @anXmlString as [@anXmlString]

     

    /*

    @anXmlString
    --------------------------------------------------------------

    <test1="100">
      <test2="100" Title="Tim" Description="xyz" Url="~/xyz.aspx?id=100" Roles="ttt" >
        <test2="101" Title="xxx" Description="xxx" Url="~/xyz.aspx?id=101" Roles="rrr" >
          <test2="106" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=106" Roles="ppp" />
        </test2>
        <test2="102" Title="yyy" Description="yyy" Url="~/xyz.aspx?id=102" Roles="qqq" />
        <test2="103" Title="zzz" Description="xxx" Url="~/xyz.aspx?id=103" Roles="qqq" />
      </test2>
    </test1>
    <test1="104">
      <test2="104" Title="aaa" Description="aaa" Url="~/xyz.aspx?id=104" Roles="sss" >
        <test2="105" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=105" Roles="ppp" />
      </test2>
    </test1>
    <test1="110">
      <test2="110" Title="Bq1" Description="Bq1" Url="~/xyz.aspx?id=110" Roles="aaa" >
        <test2="111" Title="Bq2" Description="Bq2" Url="~/xyz.aspx?id=111" Roles="aaa" >
          <test2="112" Title="Bq3" Description="Bq3" Url="~/xyz.aspx?id=106" Roles="aaa" />
        </test2>
      </test2>
    </test1>


    (1 row(s) affected)

    */

     

     

     

     

    Wednesday, July 18, 2007 11:02 PM
    Moderator
  • -- Try using UDFs

     

    create function dbo.GetSubTree(@ID int, @Level int)
    returns xml
    begin return
    (select ID as "@ID",
           Title as "@Title",
           Description as "@Description",
           Url as "@Url",
           Roles as "@Roles",
           case when Parent is not null then dbo.GetSubTree(ID,@Level+1) end
    from SiteMap
    where
    Parent=@ID or (@Level=1 and @ID=ID)
    order by ID
    for xml path('test1'), type)
    end

     

    GO

     

    select ID as "@ID",
           dbo.GetSubTree(ID,1)
    from SiteMap
    where Parent is null
    order by ID
    for xml path('test2'), type

     

    Thursday, July 19, 2007 10:56 AM
    Moderator

All replies

  •  

     

     I am using SQL Server Express

     

     

    Wednesday, July 18, 2007 3:31 PM
  • OK.  Could you look at this previous post and comment on whether this is the sort of thing you are trying to do; I am not completely sure what I am aiming at.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1703535&SiteID=1

    Wednesday, July 18, 2007 4:49 PM
    Moderator
  •  

     Hi Kent,

     

     I had gone through that post earlier and I think that it is different from what I want to achieve. The database contains all the details for a sitemap (such as Url, Title, Description and Roles) and the XML file which I have to generate using sql query is the Site Map file which is used in asp.net to implement the site navigation features on a website.

     

    Hope that helps!!

    Wednesday, July 18, 2007 5:05 PM
  •  

     This is the sample output that I could generate....

     

    Code Snippet

    <test2 ID="100">

      <test1 ID="100" Title="Tim" Description="xyz" Url="~/xyz.aspx?id=100 " Roles="ttt" />

      <test1 ID="101" Title="xxx" Description="xxx" Url="~/xyz.aspx?id=?101" Roles="rrr" />

      <test1 ID="102" Title="yyy" Description="yyy" Url="~/xyz.aspx?id=?102" Roles="qqq" />

      <test1 ID="103" Title="zzz" Description="zzz" Url="~/xyz.aspx?id=?103" Roles="qqq" />

    </test2>

    <test2 ID="104">

      <test1 ID="104" Title="aaa" Description="aaa" Url="~/xyz.aspx?id=?104" Roles="sss" />

      <test1 ID="105" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=?105" Roles="ppp" />

    </test2>

    <test2 ID="101">

      <test1 ID="106" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=?106" Roles="ppp" />

    </test2>

     

    Here if you see that 106 is a child of 101 but it branches out seperately... I will like to avoid this thing and have a nested tag below 101 as the parent node.

     

    Wednesday, July 18, 2007 5:32 PM
  • Is this what you are saying you want the output to look like:

     

    Code Snippet

    <test2 ID="100">
      <test1 ID="100" Title="Tim" Description="xyz" Url="~/xyz.aspx?id=100 " Roles="ttt" />
      <test1 ID="101" Title="xxx" Description="xxx" Url="~/xyz.aspx?id=?101" Roles="rrr" >
        <test1 ID="106" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=?106" Roles="ppp" />
      </test1>
      <test1 ID="102" Title="yyy" Description="yyy" Url="~/xyz.aspx?id=?102" Roles="qqq" />
      <test1 ID="103" Title="zzz" Description="zzz" Url="~/xyz.aspx?id=?103" Roles="qqq" />
    </test2>
    <test2 ID="104">
      <test1 ID="104" Title="aaa" Description="aaa" Url="~/xyz.aspx?id=?104" Roles="sss" />
      <test1 ID="105" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=?105" Roles="ppp" />
    </test2>

     

    Wednesday, July 18, 2007 7:33 PM
    Moderator
  •  

     Yeah thats the one I want. Can you tell me how did you get this??

    Wednesday, July 18, 2007 7:55 PM
  • As I have said before, I am somewhat new to using XML; I really do not know what is the best way to do what you are asking for.  Maybe Martin can show something better.  Anyway, here is what I came up with using TSQL and brute force:

     

    Code Snippet

    declare @anXmlString varchar(max)
    set @anXmlString = ''

     

    ;with nestedSiteMap as
    ( select 1 as Level,
             ID,
             Title,
             Description,
             Url,
             Roles,
             cast(str(id, 11) as varchar(121))
                 as path
        from siteMap
      where parent is null
      union all
      select level + 1,
             b.ID,
             b.Title,
             b.description,
             b.url,
             b.roles,
             cast( a.path + '/' +
                       str(b.id, 11) as varchar(121)
                 )
        from nestedSiteMap a
        join siteMap b
          on a.ID = b.parent
         and a.level < 3
    ), seqSiteMap as
    ( select row_number() over (order by path) as Seq,
             Level,
             ID,
             Title,
             Description,
             Url,
             roles
        from nestedSiteMap
    ), maxSeq as
    ( select max(seq) as maxSeq from seqSiteMap
    ), nextSite as
    ( select -1 + row_number() over(order by path)as Seq,
             Level
        from nestedSiteMap
      union all
      select seq,
             1
        from seqSiteMap a
        join maxSeq b
          on seq = maxSeq
    ), siteOutput as
    ( select a.Seq as Seq1,
             1 as Seq2,
             a.Level,
             b.Level as nextLevel,
             a.ID,
             a.Title,
             a.Description,
             a.Url,
             a.Roles
        from seqSiteMap a
        join nextSite b
          on a.seq = b.seq
      union all
      select Seq1,
             Seq2 + 1,
             Level - 1,
             nextLevel,
             ID,
             Title,
             Description,
             Url,
             Roles
        from siteOutput
      where Level > nextLevel
    )
    select @anXmlString = @anXmlString
                        + xmlLine + char(13)
    from
    (
      select seq1, seq2,
             case when level = 1  and  seq2 = 1
                  then '<test1="' + cast(id as varchar(11))
                         + '">' +char(13)
                  else '' end +
             case
               when seq2 = 1
               then replicate(' ', 2*level) + '<test2="'
                        + cast(id as varchar(11)) + '" Title="' +
                    rtrim(title) + '" Description="' +
                    rtrim(description) + '" Url="' +
                    rtrim(url) + '" Roles="' +
                    rtrim(Roles) + '"' +
                    case when nextLevel <= Level
                         then ' />'
                         else ' >'
                    end
               else '' end +
             case when seq2 > 1 and level = 1
                  then '  </test2>' + char(13) + '</test1>'
                  when seq2 > 1 and level > 1
                  then replicate(' ', 2*level) + '</test2>'
                  else ''
             end as xmlLine
        from siteOutput
    ) z
    order by seq1, seq2

     

    select @anXmlString as [@anXmlString]

     

    /*

    @anXmlString
    --------------------------------------------------------------

    <test1="100">
      <test2="100" Title="Tim" Description="xyz" Url="~/xyz.aspx?id=100" Roles="ttt" >
        <test2="101" Title="xxx" Description="xxx" Url="~/xyz.aspx?id=101" Roles="rrr" >
          <test2="106" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=106" Roles="ppp" />
        </test2>
        <test2="102" Title="yyy" Description="yyy" Url="~/xyz.aspx?id=102" Roles="qqq" />
        <test2="103" Title="zzz" Description="xxx" Url="~/xyz.aspx?id=103" Roles="qqq" />
      </test2>
    </test1>
    <test1="104">
      <test2="104" Title="aaa" Description="aaa" Url="~/xyz.aspx?id=104" Roles="sss" >
        <test2="105" Title="bbb" Description="bbb" Url="~/xyz.aspx?id=105" Roles="ppp" />
      </test2>
    </test1>
    <test1="110">
      <test2="110" Title="Bq1" Description="Bq1" Url="~/xyz.aspx?id=110" Roles="aaa" >
        <test2="111" Title="Bq2" Description="Bq2" Url="~/xyz.aspx?id=111" Roles="aaa" >
          <test2="112" Title="Bq3" Description="Bq3" Url="~/xyz.aspx?id=106" Roles="aaa" />
        </test2>
      </test2>
    </test1>


    (1 row(s) affected)

    */

     

     

     

     

    Wednesday, July 18, 2007 11:02 PM
    Moderator
  • -- Try using UDFs

     

    create function dbo.GetSubTree(@ID int, @Level int)
    returns xml
    begin return
    (select ID as "@ID",
           Title as "@Title",
           Description as "@Description",
           Url as "@Url",
           Roles as "@Roles",
           case when Parent is not null then dbo.GetSubTree(ID,@Level+1) end
    from SiteMap
    where
    Parent=@ID or (@Level=1 and @ID=ID)
    order by ID
    for xml path('test1'), type)
    end

     

    GO

     

    select ID as "@ID",
           dbo.GetSubTree(ID,1)
    from SiteMap
    where Parent is null
    order by ID
    for xml path('test2'), type

     

    Thursday, July 19, 2007 10:56 AM
    Moderator
  •  

    Thanks a lot guys. Will check on this and let you ll know if it worked. Was caught up with something else in between.

     

    Thanks once again.

    Friday, July 20, 2007 3:29 PM
  • Well, personally, I like Mark's approach better -- definitely simpler code.  WAY better than the "brute force" method.
    Friday, July 20, 2007 4:36 PM
    Moderator
  • Hi.

     

    I want to know if the code of GetSubTree functions in SQL 2000.

     

    G. Marín

    Monday, July 30, 2007 4:36 PM
  • Hi,

    I want to use the output of this in the UI of .net MVC3 to bind the sitemap to the menu so that I can get dynamic Menu...

    Pls help me....


    Mahesh Farkande

    Wednesday, July 18, 2012 8:40 AM