Теги XML-запроса поставляются сложенными, а не вложенными

У меня есть эти таблицы:

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

с этим запросом:

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')

Это возвращает мне это:

<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>

Или, без FOR XML, это:

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

С "типо" и "групо" сложены и пустые. Когда на самом деле я хочу этот результат:

<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>

то есть с "Типо", "Группой" и "Продуктом", вложенными в соответствии с.

Что я делаю неправильно?

Источник

1 ответ

Отсутствие ваших определений таблиц базы данных или ваших данных для проверки, это просто не в моей голове:

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')

В основном с новым FOR XML PATH конструкция и возможность вложения этих XML-запросов, вы получаете очень мощные и гораздо более простые в использовании инструменты для создания даже самой требовательной XML-структуры - определенно намного проще, чем грязный, непонятный FOR XML EXPLICIT построить у нас было раньше.....

Источник
Другие вопросы по тегам