none
Acessar Arquivo xml RRS feed

  • Pergunta

  • Prezados boa noite!! Acreditando ser mais prativo acessar arquivos xml, salvei um arquivo excel em xml, porem estou encontrando dificuldades pra fazer o select no mesmo. Cheguei aos quesitos:

    openxml, sp_sp_xml_preparedocument e sp_xml_removedocument

    Mas não estou conseguindo chegar ao resultado esperado.

    Segue abaixo o conteudo xml e o codigo feito, 

    Arquivo xml

    <?xml version="1.0"?>
    
    <?mso-application progid="Excel.Sheet"?>
    -<Workbook xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> -<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <Author>Marcelo Nogueira</Author> <LastAuthor>Marcelo Nogueira</LastAuthor> <Created>2014-12-27T16:38:31Z</Created> <LastSaved>2014-12-27T22:24:19Z</LastSaved> <Company>Faber Code Softwares Ltda</Company> <Version>15.00</Version> </DocumentProperties> -<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <AllowPNG/> </OfficeDocumentSettings> -<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>7335</WindowHeight> <WindowWidth>20490</WindowWidth> <WindowTopX>0</WindowTopX> <WindowTopY>0</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> -<Styles> -<Style ss:Name="Normal" ss:ID="Default"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:Color="#000000" ss:Size="11" x:Family="Swiss" ss:FontName="Calibri"/> <Interior/> <NumberFormat/> <Protection/> </Style> -<Style ss:ID="s62"> <Alignment ss:Vertical="Bottom" ss:Horizontal="Left"/> </Style> -<Style ss:ID="s63"> <Alignment ss:Vertical="Bottom" ss:Horizontal="Left"/> <Font ss:Color="#2A2A2A" ss:Size="8" x:Family="Swiss" ss:FontName="Segoe UI" ss:Bold="1"/> </Style> -<Style ss:ID="s64"> <Alignment ss:Vertical="Center" ss:WrapText="1"/> <Font ss:Color="#2A2A2A" ss:Size="8" x:Family="Swiss" ss:FontName="Segoe UI" ss:Bold="1"/> </Style> -<Style ss:ID="s65"> <Alignment ss:Vertical="Bottom"/> <Font ss:Color="#2A2A2A" ss:Size="8" x:Family="Swiss" ss:FontName="Segoe UI" ss:Bold="1"/> </Style> -<Style ss:ID="s66"> <Font ss:Color="#000000" ss:Size="8" x:Family="Swiss" ss:FontName="Segoe UI" ss:Bold="1"/> </Style> -<Style ss:ID="s67"> <Alignment ss:Vertical="Bottom" ss:Horizontal="Left"/> <Font ss:Color="#000000" ss:Size="8" x:Family="Swiss" ss:FontName="Segoe UI" ss:Bold="1"/> </Style> </Styles> -<Worksheet ss:Name="Pessoas"> -<Table ss:DefaultRowHeight="15" x:FullRows="1" x:FullColumns="1" ss:ExpandedRowCount="34" ss:ExpandedColumnCount="3"> <Column ss:Width="94.5"/> <Column ss:Width="143.25" ss:AutoFitWidth="0" ss:StyleID="s62"/> -<Row> -<Cell><Data ss:Type="String">CNPJ</Data></Cell> -<Cell><Data ss:Type="String">RAZAO</Data></Cell> -<Cell><Data ss:Type="String">TIPO</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">94.241.046/0001-46</Data></Cell> -<Cell ss:StyleID="s63"><Data ss:Type="String">A. Datum Corporation</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">43.009.122/0001-51</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">AdventureWorks Cycles</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">62.521.474/0001-36</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">Allure Bays Corp</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">65.177.567/0001-83</Data></Cell> -<Cell ss:StyleID="s65"><Data ss:Type="String">Alpine Ski House</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">39.422.895/0001-98</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">Awesome Computers</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">72.536.914/0001-82</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">Baldwin Museum of Science</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">45.677.822/0001-40</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">Blue Yonder Airlines</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">48.651.443/0001-79</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">City Power & Light</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">01.837.513/0001-14</Data></Cell> -<Cell ss:StyleID="s64"><Data ss:Type="String">Coho Vineyard</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">90.403.614/0001-07</Data></Cell> -<Cell ss:StyleID="s65"><Data ss:Type="String">Coho Winery</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">E</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">546.480.754-39</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">PAULA FERNADES</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">635.685.578-94</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">ROBERTO CARLOS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">686.212.332-74</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">JOÃO GILBERTO</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">988.906.306-98</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">PAULO RICARDO</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">754.545.893-14</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">ANTONIO MARCOS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">677.185.577-58</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">FERNANDO SOROCABA</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">772.630.152-00</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">AGNALDO TIMOTEO</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">127.716.415-00</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">SILVIO SANTOS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">632.721.306-59</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">MILTON NASCIMENTO</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">A</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">85.897.576/0001-56</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">ERNEST YONG</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">14.340.286/0001-69</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">MENINOS CONTABILISTAS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">53.941.464/0001-69</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">JR CONTABILIDADE</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">51.772.390/0001-59</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">PRINCIPES DA CONTABILIDADE</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">13.499.666/0001-88</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">PERPETONE CONTABIL</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">81.082.885/0001-17</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">ALTILUNE CONTABILIDADE</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">22.031.750/0001-63</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">PETROS ASSOCIATE CONTABIL</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">C</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">46.692.125/0001-20</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">SINDICATO DOS PETROLEIROS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">18.642.306/0001-07</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">SINDICATO DOS ESTRANGEIROS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">95.345.154/0001-21</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">SINDICATO DAS PATRULHEIRAS</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">S</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">05.763.584/0001-70</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">COCA COLA S.A</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">83.415.068/0001-69</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">GRAFICA DOS PAPELÕES</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">54.355.372/0001-60</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">FARMÁCIA SEMPRE AUXILIO</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell> </Row> -<Row> -<Cell ss:StyleID="s66"><Data ss:Type="String">24.772.475/0001-64</Data></Cell> -<Cell ss:StyleID="s67"><Data ss:Type="String">AUTO POSTO MEDINA</Data></Cell> -<Cell ss:StyleID="s66"><Data ss:Type="String">F</Data></Cell> </Row> </Table> -<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> -<PageSetup> <Header x:Margin="0.31496062000000002"/> <Footer x:Margin="0.31496062000000002"/> <PageMargins x:Top="0.78740157499999996" x:Right="0.511811024" x:Left="0.511811024" x:Bottom="0.78740157499999996"/> </PageSetup> -<Print> <ValidPrinterInfo/> <PaperSizeIndex>9</PaperSizeIndex> <HorizontalResolution>600</HorizontalResolution> <VerticalResolution>600</VerticalResolution> </Print> <Selected/> -<Panes> -<Pane> <Number>3</Number> <ActiveRow>7</ActiveRow> <ActiveCol>1</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook>

    Codigo

    Declare @Xml Xml
    Select
    @xml = convert(xml, P,2)
    from OpenRowSet (
    bulk 'c:\FaberCode\PessoasFicticias.xml',Single_Blob
    )Pessoas (P)

    Declare @hDoc int

    exec sp_xml_preparedocument @hDoc OutPut, @Xml

    Select 
    *
    from OpenXml(@hDoc,'/row/',2)
    with (
    Cnpj nvarchar(20),
    Razao varchar(300)
    )

    exec sp_xml_removedocument @hDoc

    Certo da atenção de sempre

    Marcelo Nogueira

    domingo, 18 de janeiro de 2015 01:27

Todas as Respostas