Início / coding / Perguntas / 79168239
Accepted
Leah
Asked: 2024-11-08 06:19:30 +0800 CST

Criar uma tabela XML a partir de uma tabela SQL onde os títulos das colunas SQL são a primeira linha?

  • 772

Estou tentando selecionar valores de uma tabela SQL como uma tabela XML com um formato específico.

Usando a tabela a seguir...

SELECT x.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) x(Id, FirstName, LastName)

SELECT *
FROM #t

DROP TABLE #t

Eu precisaria de uma saída XML parecida com esta...

<Table>
  <Row>
    <Column>Id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
</Table>

Consegui obter uma saída correspondente a isso usando UNION e XML PATH...

SELECT t1.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) t1(Id, FirstName, LastName);

WITH t2(xmlTable) AS (
        SELECT t3.Id 'Column'
              ,''
              ,t3.FirstName 'Column'
              ,''
              ,t3.LastName 'Column'
        FROM (
            SELECT *
            FROM (VALUES ('Id', 'FirstName', 'LastName')) t4(Id, FirstName, LastName)
            UNION ALL
            SELECT CONVERT(varchar(15), #t.Id)
                  ,CONVERT(varchar(15), #t.FirstName)
                  ,CONVERT(varchar(15), #t.LastName)
            FROM #t) t3
        FOR XML PATH ('Row'), ROOT('Table'))

SELECT t2.xmlTable
FROM t2

DROP TABLE #t

Mas isso parece muito trabalhoso. Existe uma solução mais elegante para isso? Especificamente, posso fazer isso sem o UNION, talvez usando apenas XML PATH?

sql

2 respostas

  1. Best Answer

    Tente a seguinte solução com base no XQuery nativo do SQL Server.

    É genérico, independentemente de quantas colunas a tabela tenha.

    Os nomes das colunas não são codificados em lugar nenhum.

    Pontos notáveis:

    • Primeiro, o inner SELECTestá compondo nos bastidores um XML bruto (veja abaixo).
    • Em segundo lugar, o método XQuery .query()compõe a saída desejada "visualmente" usando expressões XQuery FLWOR.
    • A expressão XPath /root/r[1]/*está limitando o FLWOR a apenas uma iteração para recuperar nomes de colunas dinamicamente chamando local-name() a função XQuery.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, FirstName VARCHAR(30), LastName VARCHAR(30));
INSERT INTO @tbl (FirstName, LastName) VALUES
('John', 'Doe'),
('Mary', 'Pollack');
-- DDL and sample data population, end

SELECT (
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<Table><Row>{
    for $x in /root/r[1]/*
    return <Column>{local-name($x)}</Column>
}</Row>{
    for $x in /root/r
    return <Row>{
        for $y in $x/*
        return <Column>{data($y)}</Column>}</Row>
}
</Table>');

    XML bruto

    <root>
  <r>
    <id>1</id>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
  </r>
  <r>
    <id>2</id>
    <FirstName>Mary</FirstName>
    <LastName>Pollack</LastName>
  </r>
</root>

    Saída

    <Table>
  <Row>
    <Column>id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
  <Row>
    <Column>2</Column>
    <Column>Mary</Column>
    <Column>Pollack</Column>
  </Row>
</Table>
    • 4

  2. Não, não é possível fazer isso em um único nível de consulta sem usar UNION ALL. O SQL Server espera que os valores dos nós sejam dados da tabela, não nomes de objetos ou colunas (que devem ser definidos estaticamente normalmente).

    Você pode usar a excelente resposta de @YitzakKabinsky, mas outra opção totalmente dinâmica é usar FOR JSON, OPENJSONagregar e consultar novamente os dados.

    SELECT
  (
    SELECT j2.[key] AS [Column]
    FROM (SELECT t.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    FOR XML PATH(''), TYPE
  )
FROM (SELECT TOP (1) * FROM #t) t

UNION ALL

SELECT
  (
    SELECT j2.[value] AS [Column]
    FROM (SELECT t.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    FOR XML PATH(''), TYPE
  )
FROM #t t
FOR XML PATH('Row'), ROOT('Table'), TYPE;

    db<>violino

    • 1

relate perguntas