I am having trouble getting the correct results using XML EXPLICIT in T-SQL (SQL Server 2008)

I am getting strange results from a query using XML EXPLICIT mode in T-SQL (SQL Server 2008).

Can someone explain what I am doing wrong?

  • Unable to login to SQL Server + SQL Server Authentication + Error: 18456
  • OpenRowset Excel read order
  • Error Log records in SSAS
  • SQL Server Management Studio cannot connect using Windows Authentication
  • SQL Server to MYSQL Migration tool
  • Trouble Connecting to sql server Login failed. “The login is from an untrusted domain and cannot be used with Windows authentication”
  • Here is my example:

    declare @parents table(id int, connection int, title nvarchar(255));
    declare @children table(id int, connection int, title nvarchar(255));
    
    insert into @parents(id, connection, title)
    values(1, 21, '1');
    insert into @parents(id, connection, title)
    values(2, 22, '2');
    insert into @parents(id, connection, title)
    values(3, 23, '3');
    insert into @parents(id, connection, title)
    values(4, 24, '4');
    insert into @parents(id, connection, title)
    values(5, 25, '5');
    insert into @parents(id, connection, title)
    values(6, 26, '6');
    
    insert into @children(id, connection, title)
    values(1, 21, '1a');
    insert into @children(id, connection, title)
    values(2, 22, '2a');
    insert into @children(id, connection, title)
    values(3, 23, '3a');
    insert into @children(id, connection, title)
    values(4, 24, '4a');
    insert into @children(id, connection, title)
    values(5, 25, '5a');
    insert into @children(id, connection, title)
    values(6, 26, '6a');
    insert into @children(id, connection, title)
    values(7, 21, '1b');
    insert into @children(id, connection, title)
    values(8, 22, '2b');
    insert into @children(id, connection, title)
    values(9, 23, '3b');
    insert into @children(id, connection, title)
    values(10, 24, '4b');
    insert into @children(id, connection, title)
    values(11, 25, '5b');
    insert into @children(id, connection, title)
    values(12, 26, '6b');
    
    select 1 as tag, null as parent,
     id as [p!1!id],
     title as [p!1!title],
     null as [c!2!id],
     null as [c!2!title]
    from @parents p
    union
     select 2 as tag, 1 as parent,
     p.id, 
     p.title, 
     c.id,
     c.title
    from @parents p, @children c
    where p.connection = c.connection
    for xml explicit
    

    and here is the strange result I am getting:

    <p id="1" title="1" />
    <p id="2" title="2" />
    <p id="3" title="3" />
    <p id="4" title="4" />
    <p id="5" title="5" />
    <p id="6" title="6">
      <c id="1" title="1a" />
      <c id="7" title="1b" />
      <c id="2" title="2a" />
      <c id="8" title="2b" />
      <c id="3" title="3a" />
      <c id="9" title="3b" />
      <c id="4" title="4a" />
      <c id="10" title="4b" />
      <c id="5" title="5a" />
      <c id="11" title="5b" />
      <c id="6" title="6a" />
      <c id="12" title="6b" />
    </p>
    

    OK – I think I need to be more specific, and include the “Sequence” portion of my problem as well as the original issue. Given the following data set:

    declare @parents table(id int, connection int, title nvarchar(255), sequence int);
    declare @children table(id int, connection int, title nvarchar(255), sequence int);
    
    insert into @parents(id, connection, title, sequence)
    values(1, 21, '1', 6), (2, 22, '2', 2), (3, 23, '3', 4), (4, 24, '4', 3), (5, 25, '5', 5), (6, 26, '6', 1);
    
    insert into @children(id, connection, title, sequence)
    values(1, 21, '1a', 2), (2, 22, '2a', 2), (3, 23, '3a', 2), (4, 24, '4a', 1), (5, 25, '5a', 2), (6, 26, '6a', 1), (7, 21, '1b', 1), (8, 22, '2b', 1), (9, 23, '3b', 1), (10, 24, '4b', 2), (11, 25, '5b', 1), (12, 26, '6b', 2);
    

    How can I get the following result that has data like this:

    <p id="6" title="6" sequence="1">
      <c id="6" title="6a" />
      <c id="12" title="6b" />
    </p>
    <p id="1" title="2" sequence="2">
     <c id="8" title="2b" sequence="1" />
     <c id="2" title="2a" sequence="2" />
    </p>
    

    with the

    elements ordered by sequence and their child elements order by sequence within each?

    Thanks for your help…I hope this more fully explains what I need, and why I am trying to use explicit mode.

    2 Solutions collect form web for “I am having trouble getting the correct results using XML EXPLICIT in T-SQL (SQL Server 2008)”

    With SQL Server 2005 and up, forget about FOR XML EXPLICIT – use FOR XML PATH instead – it’s much easier to use, more expressive, more intuitive.

    Try this:

    SELECT
        p.ID AS '@id',
        p.title AS '@title',
        p.sequence as '@sequence',
        (SELECT 
             c.ID AS '@id', 
             c.Title AS '@title',
             c.sequence as '@sequence'
         FROM @children c 
         WHERE p.connection = c.connection
         ORDER BY c.sequence
         FOR XML PATH('c'), TYPE
        )
    FROM    
        @parents p
    ORDER BY    
        p.sequence
    FOR XML PATH('p')
    

    Output will be this: (order by p.sequence on the outer scope, and c.sequence in the inner scope)

    <p id="6" title="6" sequence="1">
      <c id="6" title="6a" sequence="1" />
      <c id="12" title="6b" sequence="2" />
    </p>
    <p id="2" title="2" sequence="2">
      <c id="8" title="2b" sequence="1" />
      <c id="2" title="2a" sequence="2" />
    </p>
    <p id="4" title="4" sequence="3">
      <c id="4" title="4a" sequence="1" />
      <c id="10" title="4b" sequence="2" />
    </p>
    <p id="3" title="3" sequence="4">
      <c id="9" title="3b" sequence="1" />
      <c id="3" title="3a" sequence="2" />
    </p>
    <p id="5" title="5" sequence="5">
      <c id="11" title="5b" sequence="1" />
      <c id="5" title="5a" sequence="2" />
    </p>
    <p id="1" title="1" sequence="6">
      <c id="7" title="1b" sequence="1" />
      <c id="1" title="1a" sequence="2" />
    </p>
    

    See the MSDN documentation on what’s new in SQL Server 2005 for more hints and tips how to use the FOR XML PATH to generate XML from your database contents…

    Try this.
    Given this data:

    declare @parents table(id int, connection int, title nvarchar(255), sequence int);
    declare @children table(id int, connection int, title nvarchar(255), sequence int);
    
    insert into @parents(id, connection, title, sequence)
    values(1, 21, '1', 6), (2, 22, '2', 2), (3, 23, '3', 4), (4, 24, '4', 3), (5, 25, '5', 5), (6, 26, '6', 1);
    
    insert into @children(id, connection, title, sequence)
    values(1, 21, '1a', 2), (2, 22, '2a', 2), (3, 23, '3a', 2), (4, 24, '4a', 1), (5, 25, '5a', 2), (6, 26, '6a', 1), (7, 21, '1b', 1), (8, 22, '2b', 1), (9, 23, '3b', 1), (10, 24, '4b', 2), (11, 25, '5b', 1), (12, 26, '6b', 2);
    

    DO YOUR SELECT STATEMENT LIKE THIS:

    select p.id [@id], p.title [@title], p.sequence [@sequence], 
        (select c.id [@id], c.title [@title], c.sequence [@sequence]
         from @children c
         where c.connection = p.connection
         order by c.sequence
         for xml path('child'), type)
    from @parents p
    for xml path('parent'), type
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.