FOR XML EXPLICIT

Say I have this setup:

-- tables
declare @main table (id int, name varchar(20))
declare @subA table (id int, mid int, name varchar(20))
declare @subA1 table (id int, subAid int, name varchar(20))
declare @subA2 table (id int, subAid int, name varchar(20))
declare @subB table (id int, mid int, name varchar(20))

-- sample data
insert @main values (1, 'A')
insert @main values (2, 'B')
insert @SubA values (1, 1, 'A')
insert @SubA values (2, 1, 'B')
insert @SubA values (3, 2, 'C')
insert @SubA1 values (1, 1, 'A')
insert @SubA2 values (1, 2, 'A')
insert @SubB values (1, 1, 'A')
insert @SubB values (2, 1, 'B')
insert @SubB values (3, 2, 'C')

-- results
select m.id, m.name, a.name, a1.name, a2.name, b.name
from @main m
left outer join @SubA a on m.id = a.mid
left outer join @SubA1 a1 on a.id = a1.subAid
left outer join @SubA2 a2 on a.id = a2.subAid
left outer join @SubB b on m.id = b.mid

This returns:

  • Recursive calculation to form a tree using sql
  • Entity Framework - ExecuteFunction<T> returns null instead of exception
  • Temporary table in SQL server causing ' There is already an object named' error
  • Filter table depending on variable and use index
  • Sqlserver table Row to column data
  • How can I find what locks are involved in a process
  • 1   A   A   A   NULL    A
    1   A   A   A   NULL    B
    1   A   B   NULL    A   A
    1   A   B   NULL    A   B
    2   B   C   NULL    NULL    C
    

    If I use “for xml auto” then I get:

    <m id="1" name="A">
      <a name="A">
        <a1 name="A">
          <a2>
            <b name="A" />
            <b name="B" />
          </a2>
        </a1>
      </a>
      <a name="B">
        <a1>
          <a2 name="A">
            <b name="A" />
            <b name="B" />
          </a2>
        </a1>
      </a>
    </m>
    <m id="2" name="B">
      <a name="C">
        <a1>
          <a2>
            <b name="C" />
          </a2>
        </a1>
      </a>
    </m>
    

    However, this isn’t what I need. What I want to show is that @main is the main table which has two children: @subA and @SubB. @SubA in turn also has two children: @SubA1 and @SubA2, so I would like to get back:

    <m id="1" name="A">
      <a name="A">
        <a1 name="A"></a1>
        <a2></a2>    
      </a>
      <a name="B">
        <a1></a1>
        <a2 name="A"></a2>    
      </a>
      <b name="A" />
      <b name="B" />  
    </m>
    <m id="2" name="B">
      <a name="C">
        <a1></a1>
        <a2></a2>    
      </a>
      <b name="C" />  
    </m>
    

    I’m pretty sure that I will have to use “for xml explicit“, but out of all the attempts I have tried so far I haven’t been able to get the format that I need.

    Can anyone show an example query that will return the data in the required format?

    Thanks,
    Mark

  • Format Table Variable Output with FOR XML AUTO
  • 2 Solutions collect form web for “FOR XML EXPLICIT”

    You can also re-write query to control the xml output, Google nested FOR XML QUERY. Here is an example using FOR XML AUTO, you could probably get better control using this technique with FOR XML PATH.

    -- tables
    declare @main table (id int, name varchar(20))
    declare @subA table (id int, mid int, name varchar(20))
    declare @subA1 table (id int, subAid int, name varchar(20))
    declare @subA2 table (id int, subAid int, name varchar(20))
    declare @subB table (id int, mid int, name varchar(20))
    
    -- sample data
    insert @main values (1, 'm(1)')
    insert @main values (2, 'm(2)')
    insert @SubA values (1, 1, 'm(1)/a(1)')
    insert @SubA values (2, 1, 'm(1)/a(2)')
    insert @SubA values (3, 2, 'm(2)/a(3)')
    insert @SubA1 values (1, 1, 'a(1)/a1(1)')
    insert @SubA2 values (1, 1, 'a(1)/a2(1)')
    insert @SubA2 values (2, 2, 'a(2)/a2(2)')
    insert @SubB values (1, 1, 'm(1)/b(1)')
    insert @SubB values (2, 1, 'm(1)/b(2)')
    insert @SubB values (3, 2, 'm(2)/b(3)')
    
    SELECT  m.id
           ,m.name
           ,( SELECT    [name]
                       ,( SELECT    [name]
                          FROM      @subA1 AS a1
                          WHERE     a1.subAid = a.id
                        FOR XML AUTO, TYPE
                        )
                       ,( SELECT    [name]
                          FROM      @subA2 AS a2
                          WHERE     a2.subAid = a.id
                        FOR XML AUTO, TYPE
                        )
              FROM      @SubA AS a
              WHERE     m.id = a.mid
            FOR XML AUTO, TYPE
            )
           ,( SELECT    [name]
              FROM      @SubB AS b
              WHERE     m.id = b.mid
            FOR XML AUTO, TYPE
            )
    FROM    @main AS m
    FOR XML AUTO
    

    Returns:

    <m id="1" name="m(1)">
      <a name="m(1)/a(1)">
        <a1 name="a(1)/a1(1)" />
        <a2 name="a(1)/a2(1)" />
      </a>
      <a name="m(1)/a(2)">
        <a2 name="a(2)/a2(2)" />
      </a>
      <b name="m(1)/b(1)" />
      <b name="m(1)/b(2)" />
    </m>
    <m id="2" name="m(2)">
      <a name="m(2)/a(3)" />
      <b name="m(2)/b(3)" />
    </m>
    

    Admittedly, this doesn’t answer your question, but in general I’ve found the UNION approach with XML EXPLICIT a good approach, as the SQL is much clearer to read. An example is here:

    http://jdixon.dotnetdevelopersjournal.com/sql_2000s_for_xml_explicit_vs_sql_2005s_for_xml_path.htm

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.