none
Problem to read XML files RRS feed

  • Question

  • Hi community

    I have a problem when i try to read my XML file from disk and i found that the problem is related with 3 TAGs with attributes.

    This is my XML File :

    <?xml version="1.0" encoding="Windows-1252"?>
    <AuditFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:OECD:StandardAuditFile-Tax:PT_1.04_01">
      <Header>
        <AuditFileVersion>1.04_01</AuditFileVersion>
        <CompanyID>507706226</CompanyID>
        <TaxRegistrationNumber>507706226</TaxRegistrationNumber>
        <TaxAccountingBasis>F</TaxAccountingBasis>
        <CompanyName>My Company Name</CompanyName>
        <CompanyAddress>
          <BuildingNumber>0</BuildingNumber>
          <StreetName>Zona Industrial Do Roligo - Espargo</StreetName>
          <AddressDetail>Zona Industrial Do Roligo - Espargo   4524-909 Santa Maria Da Feira</AddressDetail>
          <City>Santa Maria Da Feira</City>
          <PostalCode>4524-909</PostalCode>
          <Region>Desconhecido</Region>
          <Country xsi:type="xsd:string">PT</Country>
        </CompanyAddress>
        <FiscalYear>2019</FiscalYear>
        <StartDate>2019-05-01</StartDate>
        <EndDate>2019-05-31</EndDate>
        <CurrencyCode xsi:type="xsd:string">EUR</CurrencyCode>
        <DateCreated>2019-06-01</DateCreated>
        <TaxEntity>GLOBAL</TaxEntity>
        <ProductCompanyTaxID>503140600</ProductCompanyTaxID>
        <SoftwareCertificateNumber>2616</SoftwareCertificateNumber>
        <ProductID>PRIMAVERA JASMIN/PRIMAVERA Business Software Solutions</ProductID>
        <ProductVersion>1.00</ProductVersion>
      </Header>
      <MasterFiles>
        <Customer>
          <CustomerID>CO501167323.192955420</CustomerID>
          <AccountID>Desconhecido</AccountID>
          <CustomerTaxID>192955420</CustomerTaxID>
          <CompanyName>Alda Guimaraes</CompanyName>
          <BillingAddress>
            <BuildingNumber>Desconheci</BuildingNumber>
            <StreetName>Desconhecido</StreetName>
            <AddressDetail>Alda Guimaraes  Rua dos Barbeitos n 335 Hab 1.1   4400-377 Vila Nova de Gaia</AddressDetail>
            <City>Desconhecido</City>
            <PostalCode>1000-000</PostalCode>
            <Region>Desconhecido</Region>
            <Country>PT</Country>
          </BillingAddress>
          <Telephone>Desconhecido</Telephone>
          <Fax>Desconhecido</Fax>
          <Email>Desconhecido</Email>
          <Website>Desconhecido</Website>
          <SelfBillingIndicator>0</SelfBillingIndicator>
        </Customer>
        <Customer>
          <CustomerID>CO501167323.999999990</CustomerID>
          <AccountID>Desconhecido</AccountID>
          <CustomerTaxID>999999990</CustomerTaxID>
          <CompanyName>Rosa Maria Alves</CompanyName>
          <BillingAddress>
            <BuildingNumber>Desconheci</BuildingNumber>
            <StreetName>Desconhecido</StreetName>
            <AddressDetail>Rosa Maria Alves  Avenida da República, 69 - 1esq   2780-160 Oeiras</AddressDetail>
            <City>Desconhecido</City>
            <PostalCode>1000-000</PostalCode>
            <Region>Desconhecido</Region>
            <Country>PT</Country>
          </BillingAddress>
          <Telephone>Desconhecido</Telephone>
          <Fax>Desconhecido</Fax>
          <Email>Desconhecido</Email>
          <Website>Desconhecido</Website>
          <SelfBillingIndicator>0</SelfBillingIndicator>
        </Customer>
        <Product>
          <ProductType>P</ProductType>
          <ProductCode>2158750T41C</ProductCode>
          <ProductGroup>Sem família</ProductGroup>
          <ProductDescription>Sabrina</ProductDescription>
          <ProductNumberCode>2158750T41C</ProductNumberCode>
        </Product>
        <Product>
          <ProductType>P</ProductType>
          <ProductCode>2159070T40P</ProductCode>
          <ProductGroup>Sem família</ProductGroup>
          <ProductDescription>Sapato Casual</ProductDescription>
          <ProductNumberCode>2159070T40P</ProductNumberCode>
        </Product>
        <TaxTable>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT</TaxCountryRegion>
            <TaxCode>NOR</TaxCode>
            <Description>Taxa Normal</Description>
            <TaxPercentage>23.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT</TaxCountryRegion>
            <TaxCode>ISE</TaxCode>
            <Description>Isento</Description>
            <TaxPercentage>0.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT</TaxCountryRegion>
            <TaxCode>RED</TaxCode>
            <Description>Taxa Reduzida</Description>
            <TaxPercentage>6.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT</TaxCountryRegion>
            <TaxCode>INT</TaxCode>
            <Description>Taxa Intermédia</Description>
            <TaxPercentage>13.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-MA</TaxCountryRegion>
            <TaxCode>NOR</TaxCode>
            <Description>Taxa Normal</Description>
            <TaxPercentage>22.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-MA</TaxCountryRegion>
            <TaxCode>ISE</TaxCode>
            <Description>Isento</Description>
            <TaxPercentage>0.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-MA</TaxCountryRegion>
            <TaxCode>INT</TaxCode>
            <Description>Taxa Intermédia</Description>
            <TaxPercentage>12.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-MA</TaxCountryRegion>
            <TaxCode>RED</TaxCode>
            <Description>Taxa Reduzida</Description>
            <TaxPercentage>5.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-AC</TaxCountryRegion>
            <TaxCode>NOR</TaxCode>
            <Description>Taxa Normal</Description>
            <TaxPercentage>18.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-AC</TaxCountryRegion>
            <TaxCode>ISE</TaxCode>
            <Description>Isento</Description>
            <TaxPercentage>0.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-AC</TaxCountryRegion>
            <TaxCode>INT</TaxCode>
            <Description>Taxa Intermédia</Description>
            <TaxPercentage>9.000000</TaxPercentage>
          </TaxTableEntry>
          <TaxTableEntry>
            <TaxType>IVA</TaxType>
            <TaxCountryRegion>PT-AC</TaxCountryRegion>
            <TaxCode>RED</TaxCode>
            <Description>Taxa Reduzida</Description>
            <TaxPercentage>4.000000</TaxPercentage>
          </TaxTableEntry>
        </TaxTable>
      </MasterFiles>
      <SourceDocuments>
        <SalesInvoices>
          <NumberOfEntries>2</NumberOfEntries>
          <TotalDebit>0</TotalDebit>
          <TotalCredit>33.980000</TotalCredit>
          <Invoice>
            <InvoiceNo>FR 501167323/1</InvoiceNo>
            <ATCUD>0</ATCUD>
            <DocumentStatus>
              <InvoiceStatus>N</InvoiceStatus>
              <InvoiceStatusDate>2019-05-03T16:16:01</InvoiceStatusDate>
              <SourceID>application-user::prijasminmar</SourceID>
              <SourceBilling>P</SourceBilling>
            </DocumentStatus>
            <Hash>XBqFOiKM7fjbitk6Zya9bTwoX/3HkE4AJBGXtZZOVNwHJ384rYO1/ePaPcd0bnh+JsA4gyvHfNinYD5iIBmejUuRl6wtlgB7Zf6z3yVlkZt8LQAmWm5leVoRanRNibbv+OW0CY+iu6Ow2YupNINChyefX+DZcL25buSGJK2pjo4=</Hash>
            <HashControl>1</HashControl>
            <Period>05</Period>
            <InvoiceDate>2019-05-03</InvoiceDate>
            <InvoiceType>FR</InvoiceType>
            <SpecialRegimes>
              <SelfBillingIndicator>0</SelfBillingIndicator>
              <CashVATSchemeIndicator>0</CashVATSchemeIndicator>
              <ThirdPartiesBillingIndicator>0</ThirdPartiesBillingIndicator>
            </SpecialRegimes>
            <SourceID>application-user::prijasminmar</SourceID>
            <SystemEntryDate>2019-05-03T16:15:59</SystemEntryDate>
            <CustomerID>CO501167323.192955420</CustomerID>
            <ShipTo>
              <Address>
                <StreetName>Rua dos Barbeitos n 335 Hab 1.1</StreetName>
                <AddressDetail>Rua dos Barbeitos n 335 Hab 1.1   4400-377 Vila Nova de Gaia</AddressDetail>
                <City>Vila Nova de Gaia</City>
                <PostalCode>4400-377</PostalCode>
                <Region>Desconhecido</Region>
                <Country>PT</Country>
              </Address>
            </ShipTo>
            <ShipFrom>
              <Address>
                <StreetName>Zona Industrial Do Roligo - Espargo</StreetName>
                <AddressDetail>Our Address Zona Industrial Do Roligo - Espargo   4524-909 Santa Maria Da Feira</AddressDetail>
                <City>Santa Maria Da Feira</City>
                <PostalCode>4524-909</PostalCode>
                <Region>Desconhecido</Region>
                <Country>PT</Country>
              </Address>
            </ShipFrom>
            <MovementStartTime>2019-05-03T18:17:58</MovementStartTime>
            <Line>
              <LineNumber>1</LineNumber>
              <ProductCode>2159070T40P</ProductCode>
              <ProductDescription>Sapato Casual</ProductDescription>
              <Quantity>1.000000</Quantity>
              <UnitOfMeasure>UN</UnitOfMeasure>
              <UnitPrice>21.870000</UnitPrice>
              <TaxPointDate>2019-05-03</TaxPointDate>
              <Description>Sapato Casual</Description>
              <CreditAmount>21.870000</CreditAmount>
              <Tax>
                <TaxType>IVA</TaxType>
                <TaxCountryRegion>PT</TaxCountryRegion>
                <TaxCode>NOR</TaxCode>
                <TaxPercentage>23.000000</TaxPercentage>
              </Tax>
              <SettlementAmount>0.000000</SettlementAmount>
            </Line>
            <DocumentTotals>
              <TaxPayable>5.03</TaxPayable>
              <NetTotal>21.87</NetTotal>
              <GrossTotal>26.90</GrossTotal>
            </DocumentTotals>
            <WithholdingTax>
              <WithholdingTaxAmount>0.000000</WithholdingTaxAmount>
            </WithholdingTax>
          </Invoice>
          <Invoice>
            <InvoiceNo>FR 501167323/2</InvoiceNo>
            <ATCUD>0</ATCUD>
            <DocumentStatus>
              <InvoiceStatus>N</InvoiceStatus>
              <InvoiceStatusDate>2019-05-06T11:31:19</InvoiceStatusDate>
              <SourceID>application-user::prijasminmar</SourceID>
              <SourceBilling>P</SourceBilling>
            </DocumentStatus>
            <Hash>PV380oXZBx3dosPdiXgJ03jo2eYl/NQvAUgW7wCIJjckRsddBhwsyWy/jq7jvIFXbnKcHsiFH4AuTaqyDvbhyzbu2fDt/wWTU1oDIIFdEw5950X/he9HCSvzoYoC0UpXNAbSoY4N2ai9ifywmmj0op2qX/EvYxLg2PEtm4P8XkQ=</Hash>
            <HashControl>1</HashControl>
            <Period>05</Period>
            <InvoiceDate>2019-05-06</InvoiceDate>
            <InvoiceType>FR</InvoiceType>
            <SpecialRegimes>
              <SelfBillingIndicator>0</SelfBillingIndicator>
              <CashVATSchemeIndicator>0</CashVATSchemeIndicator>
              <ThirdPartiesBillingIndicator>0</ThirdPartiesBillingIndicator>
            </SpecialRegimes>
            <SourceID>application-user::prijasminmar</SourceID>
            <SystemEntryDate>2019-05-06T11:31:17</SystemEntryDate>
            <CustomerID>CO501167323.999999990</CustomerID>
            <ShipTo>
              <Address>
                <StreetName>Avenida da República, 69 - 1esq</StreetName>
                <AddressDetail>Avenida da República, 69 - 1esq   2780-160 Oeiras</AddressDetail>
                <City>Oeiras</City>
                <PostalCode>2780-160</PostalCode>
                <Region>Desconhecido</Region>
                <Country>PT</Country>
              </Address>
            </ShipTo>
            <ShipFrom>
              <Address>
                <StreetName>Zona Industrial Do Roligo - Espargo</StreetName>
                <AddressDetail>Our Address Zona Industrial Do Roligo - Espargo   4524-909 Santa Maria Da Feira</AddressDetail>
                <City>Santa Maria Da Feira</City>
                <PostalCode>4524-909</PostalCode>
                <Region>Desconhecido</Region>
                <Country>PT</Country>
              </Address>
            </ShipFrom>
            <MovementStartTime>2019-05-06T13:33:16</MovementStartTime>
            <Line>
              <LineNumber>1</LineNumber>
              <ProductCode>2158750T41C</ProductCode>
              <ProductDescription>Sabrina</ProductDescription>
              <Quantity>1.000000</Quantity>
              <UnitOfMeasure>UN</UnitOfMeasure>
              <UnitPrice>12.110000</UnitPrice>
              <TaxPointDate>2019-05-06</TaxPointDate>
              <Description>Sabrina</Description>
              <CreditAmount>12.110000</CreditAmount>
              <Tax>
                <TaxType>IVA</TaxType>
                <TaxCountryRegion>PT</TaxCountryRegion>
                <TaxCode>NOR</TaxCode>
                <TaxPercentage>23.000000</TaxPercentage>
              </Tax>
              <SettlementAmount>0.000000</SettlementAmount>
            </Line>
            <DocumentTotals>
              <TaxPayable>2.79</TaxPayable>
              <NetTotal>12.11</NetTotal>
              <GrossTotal>14.90</GrossTotal>
            </DocumentTotals>
            <WithholdingTax>
              <WithholdingTaxAmount>0.000000</WithholdingTaxAmount>
            </WithholdingTax>
          </Invoice>
        </SalesInvoices>
      </SourceDocuments>
    </AuditFile>
    

    The problem is related With this 3 TAGs :

    <AuditFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:OECD:StandardAuditFile-Tax:PT_1.04_01"> <Header>

    <Country xsi:type="xsd:string">PT</Country>

    <CurrencyCode xsi:type="xsd:string">EUR</CurrencyCode>

    My query example are (i test only for one column) :

    WITH XmlFile (Contents) AS (
    SELECT CONVERT (XML, BulkColumn) 
    FROM OPENROWSET (BULK 'D:\TEST\saft_01-05-2019_31-05-2019.xml', SINGLE_BLOB) AS XmlData
    )
    SELECT 
    
    c.value('(InvoiceNo)[1]', 'varchar(10)') AS [InvoiceNo]
    		--c.value('(Hash)[1]', 'varchar(200)') AS [Hash],
    		--c.value('(InvoiceData)[1]', 'Datetime') AS [InvoiceData],
    		--c.value('(/ShipTo/Address/AddressDetail)[1]', 'varchar(50)') AS [genre],
    		--c.value('(/Line/ProductCode)[1]', 'varchar(20)') AS [Ref]
    FROM   XmlFile CROSS APPLY Contents.nodes ('(//SourceDocuments/SalesInvoices/Invoice/..)')  AS t(c);


    Curiously if i remove:

    <AuditFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:OECD:StandardAuditFile-Tax:PT_1.04_01"> 
    <Header>  ««« removing completely this TAG
    
    <Country xsi:type="xsd:string">PT</Country> ««« changing this TAG to <Country>PT</Country>
    
    <CurrencyCode xsi:type="xsd:string">EUR</CurrencyCode>
    ««« changing this TAG to <CurrencyCode>EUR</CurrencyCode>

    My query work.

    Could someone help me to solve this problem.

    Many thanks

    Luis

    Friday, July 12, 2019 11:26 PM

Answers

  • Hello Yitzhak,

    Many thanks for your help and all your consideration.

    Also, i have another problem that is different and that is related to the encoding of my XML file.

    I have read same problem with other people and they solve changing the encoding to UTF-16.

    But in my case i want to change it automatically before running my SELECT.

    This is my XML file:

    <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>
    

    Also, i have this code to retrieve all the information:

    DECLARE @xml XML
     
    SET @xml=N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.55000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>' ;
    
     
    
    select
        Tab.Col.value('ndos[1]', 'int') as [Ndos],
        Tab.Col.value('no[1]', 'int') as [no],
        Tab.Col.value('estab[1]', 'int') as [Estab],
        Tab.Col.value('obrano[1]', 'int') as [obrano],
        Tab.Col.value('dataobra[1]', 'datetime') as [dataobra],
        Tab.Col.value('nome[1]', 'varchar(60)') as [nome],
        Tab.Col.value('morada[1]', 'varchar(60)') as [morada],
        Tab.Col.value('local[1]', 'varchar(60)') as [Local],
        Tab.Col.value('codpost[1]', 'varchar(60)') as [Codpost],
        Tab.Col.value('ncont[1]', 'varchar(20)') as [ncont],
        Tab.Col.value('etotaldeb[1]', 'numeric(18,4)') as [etotaldeb],
        Tab.Col.value('moeda[1]', 'varchar(20)') as [moeda],
        Tab.Col.value('memissao[1]', 'varchar(20)') as [memissao],
        Tab1.Col1.value('referencia[1]', 'varchar(18)') as [ref],
        Tab1.Col1.value('descricao[1]', 'varchar(60)') as [descricao],
        Tab1.Col1.value('qtt[1]', 'numeric(18,4)') as QTT,
    	Tab1.Col1.value('cor[1]', 'varchar') as Cor,
    	Tab1.Col1.value('tam[1]', 'varchar') as Tam,
        Tab1.Col1.value('edebito[1]', 'numeric(18,4)') as [Edebito],
        Tab1.Col1.value('desconto[1]', 'numeric(18,4)') as [Desconto],
        Tab1.Col1.value('desc2[1]', 'numeric(18,4)') as [Desc2],
    	Tab1.Col1.value('iva[1]', 'numeric(6,2)') as [iva],
        Tab1.Col1.value('ettdeb[1]', 'numeric(18,4)') as [Ettdeb]
    from @xml.nodes('//Documento') as Tab(Col)
    cross apply Tab.Col.nodes('Linhas') as Tab1(Col1)
    GO


    Is that possible to change this part of string

    <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>

    to

    <?xml version = "1.0" encoding="UTF-16" standalone="yes"?>

    before running my Select statment ?

    Many thanks.

    Best regrads,

    Luis

    • Marked as answer by lafs Sunday, July 14, 2019 8:33 PM
    Sunday, July 14, 2019 4:19 PM
  • Hi Luis,

    SQL Server converts implicitly any XML content into UTF-16 encoding internally and removes a prolog in its entirety whatever it holds.

    Just remove N' at the beginning of the actual XML string and it will work for you.

    DECLARE @xml XML;
     
    SET @xml='<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.55000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>';
    
    SELECT Tab.Col.value('ndos[1]', 'int') as [Ndos],
        Tab.Col.value('no[1]', 'int') as [no],
        Tab.Col.value('estab[1]', 'int') as [Estab],
        Tab.Col.value('obrano[1]', 'int') as [obrano],
        Tab.Col.value('dataobra[1]', 'datetime') as [dataobra],
        Tab.Col.value('nome[1]', 'varchar(60)') as [nome],
        Tab.Col.value('morada[1]', 'varchar(60)') as [morada],
        Tab.Col.value('local[1]', 'varchar(60)') as [Local],
        Tab.Col.value('codpost[1]', 'varchar(60)') as [Codpost],
        Tab.Col.value('ncont[1]', 'varchar(20)') as [ncont],
        Tab.Col.value('etotaldeb[1]', 'numeric(18,4)') as [etotaldeb],
        Tab.Col.value('moeda[1]', 'varchar(20)') as [moeda],
        Tab.Col.value('memissao[1]', 'varchar(20)') as [memissao],
        Tab1.Col1.value('referencia[1]', 'varchar(18)') as [ref],
        Tab1.Col1.value('descricao[1]', 'varchar(60)') as [descricao],
        Tab1.Col1.value('qtt[1]', 'numeric(18,4)') as QTT,
    	Tab1.Col1.value('cor[1]', 'varchar') as Cor,
    	Tab1.Col1.value('tam[1]', 'varchar') as Tam,
        Tab1.Col1.value('edebito[1]', 'numeric(18,4)') as [Edebito],
        Tab1.Col1.value('desconto[1]', 'numeric(18,4)') as [Desconto],
        Tab1.Col1.value('desc2[1]', 'numeric(18,4)') as [Desc2],
    	Tab1.Col1.value('iva[1]', 'numeric(6,2)') as [iva],
        Tab1.Col1.value('ettdeb[1]', 'numeric(18,4)') as [Ettdeb]
    FROM @xml.nodes('/VFPDataSet/Documento') as Tab(Col)
    	CROSS APPLY Tab.Col.nodes('Linhas') as Tab1(Col1);

    Output:

    Ndos	no	Estab	obrano	dataobra	nome	morada	Local	Codpost	ncont	etotaldeb	moeda	memissao	ref	descricao	QTT	Cor	Tam	Edebito	Desconto	Desc2	iva	Ettdeb
    38	1	0	2	2019-07-04 00:00:00.000	PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.	ZONA INDUSTRIAL DO ROLIGO	ESPARGO	4524-909 SANTA MARIA DA FEIRA	501167323	275.0000	EURO	EURO	2143441	Bota Casual HOMEM 40/45 Cx10 - Way	15.0000			10.0000	0.0000	0.0000	23.00	150.0000
    38	1	0	2	2019-07-04 00:00:00.000	PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.	ZONA INDUSTRIAL DO ROLIGO	ESPARGO	4524-909 SANTA MARIA DA FEIRA	501167323	275.0000	EURO	EURO	2161290	Chinelo M/ Dedo SENHORA 36/41	5.5500			25.0000	0.0000	0.0000	23.00	125.0000


    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    • Edited by Yitzhak Khabinsky Sunday, July 14, 2019 6:51 PM
    • Marked as answer by lafs Sunday, July 14, 2019 8:33 PM
    Sunday, July 14, 2019 4:50 PM

All replies

  • Hi Luis,

    There is nothing wrong with the XML file.

    The SQL statement was just missing a default namespace.

    I also adjusted some XPath expressions, and modified a fully qualified path for the XML file to make it working in my environment (you would need to revert it back).

    Here is a working query:

    WITH XMLNAMESPACES (DEFAULT 'urn:OECD:StandardAuditFile-Tax:PT_1.04_01'), XmlFile (Contents) AS
    (
    	SELECT CONVERT (XML, BulkColumn) 
    	FROM OPENROWSET(BULK 'E:\TEMP\saft_01-05-2019_31-05-2019.xml', SINGLE_BLOB) AS XmlData
    )
    SELECT c.value('(InvoiceNo)[1]', 'VARCHAR(10)') AS [InvoiceNo]
    	, c.value('(Hash)[1]', 'varchar(200)') AS [Hash]
    	, c.value('(InvoiceDate)[1]', 'DATE') AS [InvoiceDate]
    	, c.value('(ShipTo/Address/AddressDetail)[1]', 'VARCHAR(50)') AS AddressDetail
    	, c.value('(Line/ProductCode)[1]', 'varchar(20)') AS ProductCode
    FROM   XmlFile CROSS APPLY Contents.nodes('(/AuditFile/SourceDocuments/SalesInvoices/Invoice)')  AS t(c);

    Output:

    InvoiceNo	Hash	InvoiceDate	AddressDetail	ProductCode
    FR 5011673	XBqFOiKM7fjbitk6Zya9bTwoX/3HkE4AJBGXtZZOVNwHJ384rYO1/ePaPcd0bnh+JsA4gyvHfNinYD5iIBmejUuRl6wtlgB7Zf6z3yVlkZt8LQAmWm5leVoRanRNibbv+OW0CY+iu6Ow2YupNINChyefX+DZcL25buSGJK2pjo4=	2019-05-03	Rua dos Barbeitos n 335 Hab 1.1   4400-377 Vila No	2159070T40P
    FR 5011673	PV380oXZBx3dosPdiXgJ03jo2eYl/NQvAUgW7wCIJjckRsddBhwsyWy/jq7jvIFXbnKcHsiFH4AuTaqyDvbhyzbu2fDt/wWTU1oDIIFdEw5950X/he9HCSvzoYoC0UpXNAbSoY4N2ai9ifywmmj0op2qX/EvYxLg2PEtm4P8XkQ=	2019-05-06	Avenida da República, 69 - 1esq   2780-160 Oeiras	2158750T41C
    Sunday, July 14, 2019 7:19 AM
  • Hello Yitzhak,

    Many thanks for your help and all your consideration.

    Also, i have another problem that is different and that is related to the encoding of my XML file.

    I have read same problem with other people and they solve changing the encoding to UTF-16.

    But in my case i want to change it automatically before running my SELECT.

    This is my XML file:

    <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>
    

    Also, i have this code to retrieve all the information:

    DECLARE @xml XML
     
    SET @xml=N'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.55000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>' ;
    
     
    
    select
        Tab.Col.value('ndos[1]', 'int') as [Ndos],
        Tab.Col.value('no[1]', 'int') as [no],
        Tab.Col.value('estab[1]', 'int') as [Estab],
        Tab.Col.value('obrano[1]', 'int') as [obrano],
        Tab.Col.value('dataobra[1]', 'datetime') as [dataobra],
        Tab.Col.value('nome[1]', 'varchar(60)') as [nome],
        Tab.Col.value('morada[1]', 'varchar(60)') as [morada],
        Tab.Col.value('local[1]', 'varchar(60)') as [Local],
        Tab.Col.value('codpost[1]', 'varchar(60)') as [Codpost],
        Tab.Col.value('ncont[1]', 'varchar(20)') as [ncont],
        Tab.Col.value('etotaldeb[1]', 'numeric(18,4)') as [etotaldeb],
        Tab.Col.value('moeda[1]', 'varchar(20)') as [moeda],
        Tab.Col.value('memissao[1]', 'varchar(20)') as [memissao],
        Tab1.Col1.value('referencia[1]', 'varchar(18)') as [ref],
        Tab1.Col1.value('descricao[1]', 'varchar(60)') as [descricao],
        Tab1.Col1.value('qtt[1]', 'numeric(18,4)') as QTT,
    	Tab1.Col1.value('cor[1]', 'varchar') as Cor,
    	Tab1.Col1.value('tam[1]', 'varchar') as Tam,
        Tab1.Col1.value('edebito[1]', 'numeric(18,4)') as [Edebito],
        Tab1.Col1.value('desconto[1]', 'numeric(18,4)') as [Desconto],
        Tab1.Col1.value('desc2[1]', 'numeric(18,4)') as [Desc2],
    	Tab1.Col1.value('iva[1]', 'numeric(6,2)') as [iva],
        Tab1.Col1.value('ettdeb[1]', 'numeric(18,4)') as [Ettdeb]
    from @xml.nodes('//Documento') as Tab(Col)
    cross apply Tab.Col.nodes('Linhas') as Tab1(Col1)
    GO


    Is that possible to change this part of string

    <?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>

    to

    <?xml version = "1.0" encoding="UTF-16" standalone="yes"?>

    before running my Select statment ?

    Many thanks.

    Best regrads,

    Luis

    • Marked as answer by lafs Sunday, July 14, 2019 8:33 PM
    Sunday, July 14, 2019 4:19 PM
  • Hi Luis,

    SQL Server converts implicitly any XML content into UTF-16 encoding internally and removes a prolog in its entirety whatever it holds.

    Just remove N' at the beginning of the actual XML string and it will work for you.

    DECLARE @xml XML;
     
    SET @xml='<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.55000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>';
    
    SELECT Tab.Col.value('ndos[1]', 'int') as [Ndos],
        Tab.Col.value('no[1]', 'int') as [no],
        Tab.Col.value('estab[1]', 'int') as [Estab],
        Tab.Col.value('obrano[1]', 'int') as [obrano],
        Tab.Col.value('dataobra[1]', 'datetime') as [dataobra],
        Tab.Col.value('nome[1]', 'varchar(60)') as [nome],
        Tab.Col.value('morada[1]', 'varchar(60)') as [morada],
        Tab.Col.value('local[1]', 'varchar(60)') as [Local],
        Tab.Col.value('codpost[1]', 'varchar(60)') as [Codpost],
        Tab.Col.value('ncont[1]', 'varchar(20)') as [ncont],
        Tab.Col.value('etotaldeb[1]', 'numeric(18,4)') as [etotaldeb],
        Tab.Col.value('moeda[1]', 'varchar(20)') as [moeda],
        Tab.Col.value('memissao[1]', 'varchar(20)') as [memissao],
        Tab1.Col1.value('referencia[1]', 'varchar(18)') as [ref],
        Tab1.Col1.value('descricao[1]', 'varchar(60)') as [descricao],
        Tab1.Col1.value('qtt[1]', 'numeric(18,4)') as QTT,
    	Tab1.Col1.value('cor[1]', 'varchar') as Cor,
    	Tab1.Col1.value('tam[1]', 'varchar') as Tam,
        Tab1.Col1.value('edebito[1]', 'numeric(18,4)') as [Edebito],
        Tab1.Col1.value('desconto[1]', 'numeric(18,4)') as [Desconto],
        Tab1.Col1.value('desc2[1]', 'numeric(18,4)') as [Desc2],
    	Tab1.Col1.value('iva[1]', 'numeric(6,2)') as [iva],
        Tab1.Col1.value('ettdeb[1]', 'numeric(18,4)') as [Ettdeb]
    FROM @xml.nodes('/VFPDataSet/Documento') as Tab(Col)
    	CROSS APPLY Tab.Col.nodes('Linhas') as Tab1(Col1);

    Output:

    Ndos	no	Estab	obrano	dataobra	nome	morada	Local	Codpost	ncont	etotaldeb	moeda	memissao	ref	descricao	QTT	Cor	Tam	Edebito	Desconto	Desc2	iva	Ettdeb
    38	1	0	2	2019-07-04 00:00:00.000	PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.	ZONA INDUSTRIAL DO ROLIGO	ESPARGO	4524-909 SANTA MARIA DA FEIRA	501167323	275.0000	EURO	EURO	2143441	Bota Casual HOMEM 40/45 Cx10 - Way	15.0000			10.0000	0.0000	0.0000	23.00	150.0000
    38	1	0	2	2019-07-04 00:00:00.000	PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.	ZONA INDUSTRIAL DO ROLIGO	ESPARGO	4524-909 SANTA MARIA DA FEIRA	501167323	275.0000	EURO	EURO	2161290	Chinelo M/ Dedo SENHORA 36/41	5.5500			25.0000	0.0000	0.0000	23.00	125.0000


    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    • Edited by Yitzhak Khabinsky Sunday, July 14, 2019 6:51 PM
    • Marked as answer by lafs Sunday, July 14, 2019 8:33 PM
    Sunday, July 14, 2019 4:50 PM
  • Hello Yitzhak,

    So simple, unbelievable.

    you save my Day.

    Thank you very much.

    Best regards,

    Luis

    Sunday, July 14, 2019 6:28 PM
  • Hi Luis,

    Glad to hear that everything is working for you now.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Please find below a bonus for you. A better naming convention to signify parent vs. child hierarchy:

    DECLARE @xml XML = '<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
    <VFPDataSet>
    	<Documento>
    		<ndos>38</ndos>
    		<no>1</no>
    		<estab>0</estab>
    		<obrano>2</obrano>
    		<dataobra>2019-07-04T00:00:00</dataobra>
    		<nome>PLANITOI IMPORTAÇÃO EXPORTAÇÃO, S.A.</nome>
    		<morada>ZONA INDUSTRIAL DO ROLIGO</morada>
    		<local>ESPARGO</local>
    		<codpost>4524-909 SANTA MARIA DA FEIRA</codpost>
    		<ncont>501167323</ncont>
    		<etotaldeb>275.000000</etotaldeb>
    		<moeda>EURO</moeda>
    		<memissao>EURO</memissao>
    		<obranome/>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>1</linha>
    			<referencia>2143441</referencia>
    			<descricao>Bota Casual HOMEM 40/45 Cx10 - Way</descricao>
    			<qtt>15.0000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>10.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>150.000000</ettdeb>
    		</Linhas>
    		<Linhas>
    			<versao>8.0</versao>
    			<linha>2</linha>
    			<referencia>2161290</referencia>
    			<descricao>Chinelo M/ Dedo SENHORA 36/41</descricao>
    			<qtt>5.55000</qtt>
    			<cor/>
    			<tam/>
    			<edebito>25.000000</edebito>
    			<desconto>.00</desconto>
    			<desc2>.00</desc2>
    			<iva>23.00</iva>
    			<ettdeb>125.000000</ettdeb>
    		</Linhas>
    	</Documento>
    </VFPDataSet>';
    
    SELECT parent.col.value('ndos[1]', 'int') as [Ndos],
        parent.col.value('no[1]', 'int') as [no],
        parent.col.value('estab[1]', 'int') as [Estab],
        parent.col.value('obrano[1]', 'int') as [obrano],
        parent.col.value('dataobra[1]', 'datetime') as [dataobra],
        parent.col.value('nome[1]', 'varchar(60)') as [nome],
        parent.col.value('morada[1]', 'varchar(60)') as [morada],
        parent.col.value('local[1]', 'varchar(60)') as [Local],
        parent.col.value('codpost[1]', 'varchar(60)') as [Codpost],
        parent.col.value('ncont[1]', 'varchar(20)') as [ncont],
        parent.col.value('etotaldeb[1]', 'numeric(18,4)') as [etotaldeb],
        parent.col.value('moeda[1]', 'varchar(20)') as [moeda],
        parent.col.value('memissao[1]', 'varchar(20)') as [memissao],
        child.col.value('referencia[1]', 'varchar(18)') as [ref],
        child.col.value('descricao[1]', 'varchar(60)') as [descricao],
        child.col.value('qtt[1]', 'numeric(18,4)') as QTT,
    	child.col.value('cor[1]', 'varchar') as Cor,
    	child.col.value('tam[1]', 'varchar') as Tam,
        child.col.value('edebito[1]', 'numeric(18,4)') as [Edebito],
        child.col.value('desconto[1]', 'numeric(18,4)') as [Desconto],
        child.col.value('desc2[1]', 'numeric(18,4)') as [Desc2],
    	child.col.value('iva[1]', 'numeric(6,2)') as [iva],
        child.col.value('ettdeb[1]', 'numeric(18,4)') as [Ettdeb]
    FROM @xml.nodes('/VFPDataSet/Documento') as parent(col)
    	CROSS APPLY parent.col.nodes('Linhas') as child(col);
    Sunday, July 14, 2019 6:40 PM