Compartilhar via

Suporte a FOR XML para os tipo de dados XML

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

Se uma consulta FOR XML especificar uma coluna do tipo xml na cláusula SELECT, os valores da coluna serão mapeados como elementos no XML retornado, independentemente da diretiva ELEMENTS estar especificada. Qualquer declaração XML na coluna de tipo xml não é serializada.

Por exemplo, a consulta a seguir recupera informações de contato do cliente, tais como as colunas BusinessEntityID, FirstNamee LastName , e os números de telefone da coluna AdditionalContactInfo de tipo xml .

USE AdventureWorks2022;
GO

SELECT BusinessEntityID,
       FirstName,
       LastName,
       AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number
') AS PhoneNumber
FROM Person.Person
WHERE AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number
') IS NOT NULL
FOR XML AUTO, TYPE;

Como a consulta não especifica a diretivaELEMENTS, os valores da coluna são retornados como atributos, exceto pelos valores adicionais de informações de contato recuperados da coluna do tipo xml. Esses são retornados como elementos.

Este é o resultado parcial:

<Person.Person BusinessEntityID="291" FirstName="Gustavo" LastName="Achong">
  <PhoneNumber>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1112</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1111</act:number>
  </PhoneNumber>
</Person.Person>
<Person.Person BusinessEntityID="293" FirstName="Catherine" LastName="Abel">
  <PhoneNumber>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-2222</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-1234</act:number>
  </PhoneNumber>
</Person.Person>
...

Se você especificar um alias para a coluna XML gerada pela XQuery, esse alias será usado para adicionar um elemento wrapper em torno do XML gerado pela XQuery. Por exemplo, a seguinte consulta especifica MorePhoneNumbers como um alias de coluna:

SELECT BusinessEntityID,
       FirstName,
       LastName,
       AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number
') AS PhoneNumber
FROM Person.Person
WHERE AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number
') IS NOT NULL
FOR XML AUTO, TYPE;

O XML retornado pelo XQuery é envolvido no elemento <MorePhoneNumbers>, conforme mostra o seguinte resultado parcial:

<Person.Person BusinessEntityID="291" FirstName="Gustavo" LastName="Achong">
  <MorePhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1112</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1111</act:number>
  </MorePhoneNumbers>
</Person.Person>
<Person.Person BusinessEntityID="293" FirstName="Catherine" LastName="Abel">
  <MorePhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-2222</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-1234</act:number>
  </MorePhoneNumbers>
</Person.Person>
...

Se você especificar a diretiva ELEMENTS na consulta, BusinessEntityID, LastName, e FirstName são retornados como elementos no XML resultante.

O exemplo a seguir ilustra que a lógica de processamento de FOR XML não serializa qualquer declaração XML nos dados XML de uma coluna do tipo xml:

CREATE TABLE t
(
    i INT,
    x XML
);
GO

INSERT INTO t
VALUES (1, '<?xml version="1.0" encoding="UTF-8" ?>
                             <Root SomeID="10" />');

SELECT i, x
FROM t
FOR XML AUTO;

Veja a seguir o conjunto de resultados. No resultado, a declaração XML <?xml version="1.0" encoding="UTF-8" ?> não é serializada.

<root>
  <t i="1">
    <x>
      <Root SomeID="10" />
    </x>
  </t>
</root>

Retornar o XML de uma função definida pelo usuário

FOR XML as consultas podem ser usadas para retornar XML de uma função definida pelo usuário que retorna um dos seguintes:

  • Uma tabela com uma única coluna de tipo xml
  • Uma instância do tipo xml

Por exemplo, a seguinte função definida pelo usuário retorna uma tabela com uma única coluna de tipo xml:

USE AdventureWorks2022;
GO

CREATE FUNCTION dbo.MyUDF (@ProductModelID INT)
RETURNS @T TABLE (ProductDescription XML)
AS
BEGIN
    INSERT @T
    SELECT CatalogDescription.query('
declare namespace PD="https://www.adventure-works.com/schemas/products/description";
                    //PD:ProductDescription  ')
    FROM Production.ProductModel
    WHERE ProductModelID = @ProductModelID;
    RETURN;
END

Você pode executar a função definida pelo usuário e consultar a tabela retornada por ela. Neste exemplo, o XML retornado pela consulta da tabela é atribuído a uma variável de tipo xml .

DECLARE @x AS XML;

SET @x = (SELECT * FROM MyUDF(19));

SELECT @x;

Esse é outro exemplo de uma função definida pelo usuário. Essa função definida pelo usuário retorna uma instância do tipo xml . Neste exemplo, a função definida pelo usuário retorna uma instância XML com tipo, porque o namespace do esquema está especificado.

DROP FUNCTION dbo.MyUDF;
GO

CREATE FUNCTION MyUDF (@ProductModelID INT)
RETURNS XML([Production].[ProductDescriptionSchemaCollection])
AS
BEGIN
    DECLARE @x AS XML;
    SET @x = (SELECT CatalogDescription
              FROM Production.ProductModel
              WHERE ProductModelID = @ProductModelID);
    RETURN @x;
END

O XML retornado pela função definida pelo usuário pode ser atribuído a uma variável de tipo xml da seguinte maneira:

DECLARE @x AS XML;

SELECT @x = dbo.MyUDF(19);

SELECT @x;

Conteúdo relacionado

  • Last updated on