none
GERAR XML BASEADO EM SCHEMA RRS feed

  • Pergunta

  • Qual a maneira mais fácil de gerar um arquivo XML lendo as tabelas do SQL  e utilizando o SCHEMA ?
    Como poderia fazer isso em um comando transaction sql?

    Por favor me passe um exemplo.

    Um exemplo: é do site do SUFRAMA
    <?xml version="1.0" encoding="UTF-8"?>
    <schema xmlns="http://www.w3.org/2001/XMLSchema"
            targetNamespace="http://www.portal.fucapi.br"
            elementFormDefault="qualified">
     <element name="lote">
      <complexType>
       <sequence>
        <element name="cnpjDestinatario" type="string" nillable="false"></element>
        <element name="cnpjTransp" type="string" nillable="true"></element>
        <element name="inscSufDestinatario" type="string" nillable="false"></element>
        <element name="ufDestino" type="string" nillable="false"></element>
        <element name="ufOrigem" type="string" nillable="false"></element>
        <element name="qtdeNF" type="long" nillable="false"></element>
        <element name="notasFiscais">
         <complexType>
          <sequence>
           <element name="notaFiscal" minOccurs="1" maxOccurs="50">
            <complexType>
             <sequence>
              <element name="cnpjRemetente" type="string" nillable="false"></element>
              <element name="CFOP" type="long" nillable="false"></element>
              <element name="modelo" type="string" nillable="false"></element>
              <element name="serie" type="string" nillable="false"></element>
              <element name="inscEstDestinatario" type="string" nillable="true"></element>
              <element name="dtSaidaNF" type="string" nillable="true">
               <annotation>
                <documentation>dd/MM/yyyy</documentation>
               </annotation>
              </element>
              <element name="hrSaidaNF" type="string" nillable="true">
               <annotation>
                <documentation>hh:mm</documentation>
               </annotation>
              </element>
              <element name="optDebito" type="string" nillable="true"></element>
              <element name="ddAdicionais" type="string" nillable="true">
               <annotation>
                <documentation>dados adicionais sobre a nota fiscal</documentation>
               </annotation>
              </element>
              <element name="valores">
               <complexType>
                <sequence>
                 <element name="baseCalcICMS" type="string" nillable="true"></element>
                 <element name="valICMS" type="string" nillable="true"></element>
                 <element name="valFT" type="string" nillable="true">
                  <annotation>
                   <documentation>Valor do frete</documentation>
                  </annotation>
                 </element>
                 <element name="valSeguro" type="string" nillable="true"></element>
                 <element name="valTotIPI" type="string" nillable="true"></element>
                 <element name="valOutrasDesp" type="string" nillable="true"></element>
                 <element name="valTotItens" type="double" nillable="false"></element>
                 <element name="valTotNF" type="double" nillable="false"></element>
                 <element name="valPIS" type="string" nillable="true"></element>
                 <element name="valCOFINS" type="string" nillable="true"></element>
                 <element name="valAbatICMS" type="string" nillable="true">
                  <annotation>
                   <documentation>Em vigor ao convenio 6588</documentation>
                  </annotation>
                 </element>
                </sequence>
               </complexType>
              </element>
              <element name="transportador">
               <complexType>
                <sequence>
                 <element name="cnpjTransp" type="string" nillable="false"></element>
                 <element name="ftConta" type="string" nillable="true"></element>
                 <element name="placaVeic" type="string" nillable="true"></element>
                 <element name="ufPlacaVeic" type="string" nillable="true"></element>
                 <element name="inscEstTransp" type="string" nillable="true"></element>
                 <element name="qtdeVol" type="string" nillable="true"></element>
                 <element name="especie" type="string" nillable="true"></element>
                 <element name="marca" type="string" nillable="true"></element>
                 <element name="numero" type="string" nillable="true"></element>
                 <element name="pesoBruto" type="string" nillable="true"></element>
                 <element name="pesoLiq" type="string" nillable="true"></element>
                </sequence>
               </complexType>
              </element>
              <element name="gnre">
               <complexType>
                <sequence>
                 <element name="valGNRE" type="string" nillable="true"></element>
                 <element name="dtVencGNRE" type="string" nillable="true">
                  <annotation>
                   <documentation>dd/MM/yyyy</documentation>
                  </annotation>
                 </element>
                 <element name="perRefGNRE" type="string" nillable="true">
                  <annotation>
                   <documentation>Periodo referente GNRE (MM/yyyy)</documentation>
                  </annotation>
                 </element>
                </sequence>
               </complexType>
              </element>
              <element name="refaturamento">
               <complexType>
                <sequence>
                 <element name="NFRefat" type="string" nillable="true"></element>
                 <element name="dtEmissaoRefat" type="string" nillable="true">
                  <annotation>
                   <documentation>dd/MM/yyyy</documentation>
                  </annotation>
                 </element>
                 <element name="inscSufRefat" type="string" nillable="true"></element>
                </sequence>
               </complexType>
              </element>
              <element name="substTributaria">
               <complexType>
                <sequence>
                 <element name="baseCalcICMSSubTrib" type="string" nillable="true"></element>
                 <element name="valICMSSub" type="string" nillable="true"></element>
                 <element name="inscEstSubTrib" type="string" nillable="true"></element>            
                </sequence>
               </complexType>
              </element>
              <element name="itens">
               <complexType>
                <sequence>
                 <element name="item" minOccurs="1" maxOccurs="1000">
                  <complexType>
                   <sequence>
                    <element name="codProd" type="string" nillable="false"></element>
                    <element name="descItem" type="string" nillable="false"></element>
                    <element name="codNCM" type="string" nillable="true"></element>
                    <element name="unidMed" type="string" nillable="false"></element>
                    <element name="valUnit" type="double" nillable="false"></element>
                    <element name="qtde" type="long" nillable="false"></element>
                    <element name="valTot" type="double" nillable="false"></element>
                    <element name="classFiscal" type="string" nillable="true"></element>
                    <element name="sitTribut" type="string" nillable="true"></element>
                    <element name="alICMS" type="string" nillable="true"></element>
                    <element name="alIPI" type="string" nillable="true"></element>
                    <element name="valIPI" type="string" nillable="true"></element>
                   </sequence>
                  </complexType>
                 </element>
                </sequence>
               </complexType>
              </element>
             </sequence>
             <attribute name="nro" type="long" use="required"></attribute>
             <attribute name="dtEmissao" type="string" use="required">
              <annotation>
               <documentation>dd/MM/yyyy</documentation>
              </annotation>
             </attribute>
             <attribute name="txZero" type="boolean" use="required"></attribute>
             <attribute name="incent" type="long" use="required"></attribute>
            </complexType>
           </element>
          </sequence>
         </complexType>
        </element>
       </sequence>
       <attribute name="nro" type="long" use="required"></attribute>
       <attribute name="versao_sw" type="string" use="optional"></attribute>
       <attribute name="dtEmissao" type="string" use="required">
        <annotation>
         <documentation>dd/MM/yyyy</documentation>
        </annotation>
       </attribute>
      </complexType>
     </element>
    </schema>


    !DSPAM:46aa3cac145111235816653!


    !Controle de SPAM:46aa3cad194292966911760!


    Maria
    quarta-feira, 19 de agosto de 2009 17:42

Todas as Respostas

  • Olá,

    Isso é um arquivo de validação XML. O que você queria fazer mais especificamente?

    gerar um arquivo desses com os dados das system tables?

    Abraços
    Demétrio Silva
    quarta-feira, 19 de agosto de 2009 17:59
  • maria,

    XML não é o meu forte... mas acredito que este link do bol pode te  ajudar

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/08a6f084-8f73-4f2a-bae4-3c7513dc99b9.htm


    att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    quarta-feira, 19 de agosto de 2009 18:03
  • Maria,

    Provavelmente é mais fácil e simples lêr a tabela e gerar o xml ao invês de lêr todo schema.

    Select * from Produtos For XML Auto

     


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    quarta-feira, 19 de agosto de 2009 19:59