Acessar Arquivo xml

Question

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

Answer 1

Marcelo, tudo bem?

Escrevi há algum tempo um post sobre leitura de arquivos XML a partir do SQL. Deixo aqui o link, pois acredito que o mesmo pode ser útil a você:

http://www.devmedia.com.br/carregando-o-conteudo-de-arquivos-xml-em-tabelas-do-sql-server-com-t-sql/28183

Abs.

Answer 2

Tudo bem Renato!! Estava analisando exatamente o seu post antes da sua resposta, porem a estrutura do xml é diferente, acredito por não estar muito familiarizado estou encontrando grande dificuldade para chegar ao resultado desejado!!

Estou tentando, mas obrigado pela ajuda

Marcelo

Answer 3

Marcelo,

No caso, o exemplo do post era apenas uma das estruturas possíveis de XML. Por ser um documento hierárquico, arquivos XML podem ter as mais variáveis estruturas. Tente fazer testes até chegar ao nível que vc precisa do arquivo, isto ajuda a entender melhor como os comandos para leitura de XML funcionam.

Abs

Answer 4

Marcelinho,

Como o Renato indicou, a estrutura de um arquivo XML pode variar muito. No caso de um arquivo XML criado à partir do Excel então a estrutura pode ser mais complexa ainda, porque cada célula convertida também exporta características de lay-out e formatação para uso em outras ferramentas, inclusive o próprio Excel.

Existem vários modos de leitura de um arquivo XML, segue alguns documentos do BOL:

https://technet.microsoft.com/pt-br/library/ms191268(v=sql.105).aspx

https://technet.microsoft.com/pt-br/library/ms175894(v=sql.105).aspx

https://technet.microsoft.com/pt-br/library/ms191231(v=sql.105).aspx

https://technet.microsoft.com/pt-br/library/ms190965(v=sql.105).aspx

Como você pode ver, ler um arquivo XML pode ter diferentes modos, dependendo da informação que você pretende obter (valor do nó, atributo, ...). Eu sugiro que você procure antes ler informações de estruturas XML mais simples antes de tentar um arquivo XML produzido por uma ferramenta como o Excel.

Caso você precisar carregar um arquivo Excel no SQL Server, então veja este artigo:

http://social.technet.microsoft.com/wiki/pt-br/contents/articles/24213.importando-uma-planilha-excel-para-um-banco-de-dados-sql-server.aspx

Se ajudou na sua solução, não esqueça de marcar como resposta !

Abraços,

Durval Ramos
Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
----------------------------------
Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"