Como gerar XML no Sql Server – Parte 1

Nesse primeiro post sobre geração de XML no SQL server vamos ver como gerar um XML de forma simples. Vamos utilizar nesse primeiro post, o modo “RAW” entre 4 modos disponíveis (RAW, AUTO, EXPLICIT e PATH).

O modo “RAW” gera um elemento XML único para cada linha. Para utilizar, devemos simplesmente acrescentar a cláusula “FOR XML” e palavra-chave “RAW” para sua instrução “SELECT”. Para executar os exemplos, vamos criar uma tabela e cadastrar alguns valores.

CREATE TABLE cliente (id INT PRIMARY KEY IDENTITY(1, 1), nome VARCHAR(100), ativo BIT);

INSERT INTO cliente (nome, ativo) VALUES('João', 1);
INSERT INTO cliente (nome, ativo) VALUES('José', 1);
INSERT INTO cliente (nome, ativo) VALUES('Maria', 1);
INSERT INTO cliente (nome, ativo) VALUES('Joaquina', 0);
INSERT INTO cliente (nome, ativo) VALUES(NULL, 0);

Acima, apenas criamos uma tabela “cliente” e inserimos alguns registros.

SELECT id, nome, ativo
FROM cliente
FOR XML RAW

O “SELECT” tem apenas a clausula “FOR XML” de diferente (comparado ao um “SELECT” “tradicional”).
Se executarmos o “SELECT” acima teremos o seguinte resultado:

<row id="1" nome="João" ativo="1" />
<row id="2" nome="José" ativo="1" />
<row id="3" nome="Maria" ativo="1" />
<row id="4" nome="Joaquina" ativo="0" />

Acima temos um XML com as colunas da tabela cliente, e cada linha do XML tem um nó “row” por padrão, podemos utilizar um parâmetro para definir o valor que sera utilizado para cada linha

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente')

Acima definimos que o nome do nó deve ser cliente, utilizando “RAW(‘cliente’)”

<cliente id="1" nome="João" ativo="1" />
<cliente id="2" nome="José" ativo="1" />
<cliente id="3" nome="Maria" ativo="1" />
<cliente id="4" nome="Joaquina" ativo="0" />

Esse XML não possui um elemento raiz, para adicionar, devemos acrescentar a palavra chave “ROOT”, como segue:

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente'), ROOT

Acima definimos que o XML deve possuir um elemento raiz

<root>
  <cliente id="1" nome="João" ativo="1" />
  <cliente id="2" nome="José" ativo="1" />
  <cliente id="3" nome="Maria" ativo="1" />
  <cliente id="4" nome="Joaquina" ativo="0" />
  <cliente id="5" ativo="0" />
</root>

O nome do elemento raiz é “row”, para mudar isso, basta utilizar um parâmetro de “ROOT”

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente'), ROOT('cliente')

Acima definimos o o nome do elemento raiz, utilizando “ROOT(‘cliente’)”

<cliente>
  <cliente id="1" nome="João" ativo="1" />
  <cliente id="2" nome="José" ativo="1" />
  <cliente id="3" nome="Maria" ativo="1" />
  <cliente id="4" nome="Joaquina" ativo="0" />
  <cliente id="5" ativo="0" />
</cliente>

Elementos

Por padrão o XML ficará com seus respectivos valores nos atributos, e com o mesmo nome de coluna, para mudar isso utilize a palavra chave “ELEMENTS”

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente'), ROOT('cliente'), ELEMENTS

Acimas adicionamos “ELEMENTS”, para definir que os valores das colunas não devem ficar como atributo.

<cliente>
  <cliente>
    <id>1</id>
    <nome>João</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente>
    <id>2</id>
    <nome>José</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente>
    <id>3</id>
    <nome>Maria</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente>
    <id>4</id>
    <nome>Joaquina</nome>
    <ativo>0</ativo>
  </cliente>
  <cliente>
    <id>5</id>
    <ativo>0</ativo>
  </cliente>
</cliente>

Repare que o elemento “nome” do último nó, não está presente, isso ocorre porque o valor é “NULL”, e por padrão esses valores são omitidos. Para resolver esse detalhe, utilize a palavra chave “XSINIL”, logo depois de “ELEMENTS”

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente'), ROOT('cliente'), ELEMENTS XSINIL

<cliente xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <cliente>
    <id>1</id>
    <nome>João</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente>
    <id>2</id>
    <nome>José</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente>
    <id>3</id>
    <nome>Maria</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente>
    <id>4</id>
    <nome>Joaquina</nome>
    <ativo>0</ativo>
  </cliente>
  <cliente>
    <id>5</id>
    <nome xsi:nil="true" />
    <ativo>0</ativo>
  </cliente>
</cliente>

Note que o “xmlns” também foi adicionada ao nó raiz, e fornece o nome de exemplo para o schema. Também é possível utilizar schema no XML, com apenas a palavra chave “XMLSCHEMA”

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente'), ROOT('cliente'), ELEMENTS XSINIL, XMLSCHEMA

<cliente xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="cliente">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="id" type="sqltypes:int" nillable="1" />
          <xsd:element name="nome" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
                <xsd:maxLength value="100" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="ativo" type="sqltypes:bit" nillable="1" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <cliente xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <id>1</id>
    <nome>João</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <id>2</id>
    <nome>José</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <id>3</id>
    <nome>Maria</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <id>4</id>
    <nome>Joaquina</nome>
    <ativo>0</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <id>5</id>
    <nome xsi:nil="true" />
    <ativo>0</ativo>
  </cliente>
</cliente>

Namespace

Para mudar o namespace vamos utiliza um parâmetro para XMLSCHEMA

SELECT id, nome, ativo
FROM cliente
FOR XML RAW('cliente'), ROOT('cliente'), ELEMENTS XSINIL, XMLSCHEMA ('urn:schemas-novo.com')

Acima utilizamos um novo valor para o schema do XML com “XMLSCHEMA (‘urn:schemas-novo.com’)”

<cliente xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-novo.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="cliente">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="id" type="sqltypes:int" nillable="1" />
          <xsd:element name="nome" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
                <xsd:maxLength value="100" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="ativo" type="sqltypes:bit" nillable="1" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <cliente xmlns="urn:schemas-novo.com">
    <id>1</id>
    <nome>João</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-novo.com">
    <id>2</id>
    <nome>José</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-novo.com">
    <id>3</id>
    <nome>Maria</nome>
    <ativo>1</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-novo.com">
    <id>4</id>
    <nome>Joaquina</nome>
    <ativo>0</ativo>
  </cliente>
  <cliente xmlns="urn:schemas-novo.com">
    <id>5</id>
    <nome xsi:nil="true" />
    <ativo>0</ativo>
  </cliente>
</cliente>

Até a próxima pessoal!