XML query tags comes stacked instead of nested

Question

I have these tables:

TIPO

X5_CHAVE    X5_TABELA     X5_DESCRI
--------    ---------     ---------------------
04          02            APOIO DIDATICO 
03          02            PESQUISA E REFERENCIA 

GRUPO

BM_GRUPO     BM_DESC
--------     -----------
01           HISTORIA
05           JORNALISMO
13           PAPEL

PRODUTO

COD_ITEM       DSC_ITEM                           PRV_ITEM
----------     ------------------------------     --------
12.13.0001     PAPEL OFF SET 63,5 CM - 63 G           0.00
12.13.0002     PAPEL OFF SET 87 CM - 63 G             0.00
04.05.0001     REFLEXOES SOB A DIFER -2ªED.02        21.00
03.01.0001     BRINQUEDOS DA MEMORIA - 1996          18.00

with this query:

SELECT TOP 2
         1 AS TAG
        ,NULL AS PARENT
        ,cast(tipo.X5_DESCRI as varchar(55)) AS "tipo!1!dsc_tipo"
        ,NULL AS "Grupo!2!dsc_grupo"
        ,NULL AS "produto!3!cod_item"
        ,NULL AS "produto!3!dsc_item"
        ,NULL AS "produto!3!prv_item"
FROM SB1020 produto
JOIN SBM020 grupo ON (cast(grupo.BM_GRUPO as char(2)) = cast(substring(produto.B1_COD,4,2) as char(2)))
JOIN SX5020 tipo ON ((cast(tipo.X5_CHAVE as char(2))= cast(substring(produto.B1_COD,1,2) as char(2))) AND (tipo.X5_TABELA = '02'))
UNION ALL
SELECT TOP 2
         2 AS TAG
        ,1 AS PARENT
        ,cast(tipo.X5_DESCRI as varchar(55))
        ,cast(grupo.BM_DESC as varchar(30))
        ,NULL
        ,NULL
        ,NULL
FROM SB1020 produto
JOIN SBM020 grupo ON (cast(grupo.BM_GRUPO as char(2)) = cast(substring(produto.B1_COD,4,2) as char(2)))
JOIN SX5020 tipo ON ((cast(tipo.X5_CHAVE as char(2))= cast(substring(produto.B1_COD,1,2) as char(2))) AND (tipo.X5_TABELA = '02'))
UNION ALL
SELECT TOP 2
         3 AS TAG
        ,2 AS PARENT
        ,cast(tipo.X5_DESCRI as varchar(55))
        ,cast(grupo.BM_DESC as varchar(30))
        ,produto.B1_COD as COD_ITEM
        ,cast(produto.B1_DESC as varchar(30)) AS DSC_ITEM
        ,cast(produto.B1_PRV1 AS decimal (6,2)) as PRV_ITEM
FROM SB1020 produto
JOIN SBM020 grupo ON (cast(grupo.BM_GRUPO as char(2)) = cast(substring(produto.B1_COD,4,2) as char(2)))
JOIN SX5020 tipo ON ((cast(tipo.X5_CHAVE as char(2))= cast(substring(produto.B1_COD,1,2) as char(2))) AND (tipo.X5_TABELA = '02'))

ORDER BY "produto!3!dsc_item","Grupo!2!dsc_grupo","tipo!1!dsc_tipo"

FOR XML EXPLICIT, ROOT('dsc')

That returns me this:

<dsc>
  <tipo dsc_tipo="MATERIAL DE CONSUMO                                    " />
  <tipo dsc_tipo="MATERIAL DE CONSUMO                                    ">
    <Grupo dsc_grupo="PAPEL                         " />
    <Grupo dsc_grupo="PAPEL                         ">
      <produto cod_item="12.13.0001     " dsc_item="PAPEL OFF SET 63,5 CM - 63 G  " prv_item="0.00" />
      <produto cod_item="12.13.0002     " dsc_item="PAPEL OFF SET 87 CM - 63 G    " prv_item="0.00" />
    </Grupo>
  </tipo>
</dsc>

Or, without the FOR XML, this:

TAG     PARENT     tipo!1!dsc_tipo        Grupo!2!dsc_grupo      produto!3!cod_item       produto!3!dsc_item              produto!3!prv_item
---     ------     ---------------        -----------------      ------------------       ------------------              ------------------
1       NULL       MATERIAL DE CONSUMO    NULL                   NULL                     NULL                             NULL
1       NULL       MATERIAL DE CONSUMO    NULL                   NULL                     NULL                             NULL
2       1          MATERIAL DE CONSUMO    PAPEL                  NULL                     NULL                             NULL
2       1          MATERIAL DE CONSUMO    PAPEL                  NULL                     NULL                             NULL
3       2          MATERIAL DE CONSUMO    PAPEL                  12.13.0001               PAPEL OFF SET 63,5 CM - 63 G     0.00
3       2          MATERIAL DE CONSUMO    PAPEL                  12.13.0002               PAPEL OFF SET 87 CM - 63 G       0.00

With the "tipo" and "grupo" stacked and empty. When in fact I want this result:

<dsc>
  <tipo dsc_tipo="MATERIAL DE CONSUMO                                    ">
    <Grupo dsc_grupo="PAPEL                         ">
      <produto cod_item="12.13.0001     " dsc_item="PAPEL OFF SET 63,5 CM - 63 G  " prv_item="0.00" />
      <produto cod_item="12.13.0002     " dsc_item="PAPEL OFF SET 87 CM - 63 G    " prv_item="0.00" />
    </Grupo>
  </tipo>
</dsc>

that is, with the "Tipo" and "Grupo" and "produto" nested according to .

What am I doing wrong?

Answer 1

Not having your database table definitions, nor your data to test this with, this is just off the top of my head:

SELECT
   CAST(tipo.X5_DESCRI AS VARCHAR(55)) AS '@dsc_tipo',
   CAST(grupo.BM_DESC AS VARCHAR(30)) AS 'Grupo/@dsc_grupo',
   (SELECT
        produto.B1_COD AS '@cod_item',
        CAST(produto.B1_DESC AS VARCHAR(30)) AS '@dsc_item',
        CAST(produto.B1_PRV1 AS DECIMAL(6,2)) AS '@prv_item'
    WHERE
        CAST(grupo.BM_GRUPO AS CHAR(2)) = CAST(SUBSTRING(produto.B1_COD, 4, 2) AS CHAR(2))
    FOR XML PATH('produto'), TYPE
   ) 
FROM
    dbo.SBM020 grupo 
INNER JOIN 
    dbo.SX5020 tipo ON CAST(tipo.X5_CHAVE AS CHAR(2)) = CAST(SUBSTRING(produto.B1_COD,  1, 2) AS CHAR(2))
                    AND tipo.X5_TABELA = '02'
FOR XML PATH('tipo'), ROOT('dsc')

Basically, with the new FOR XML PATH construct, and the ability to nest those XML queries, you get very powerful and much easier to use tools to create even the most demanding XML structure - definitely a whole lot easier than the messy, incomprehensible FOR XML EXPLICIT construct we had before.....