none
Importar arquivo xml RRS feed

  • Pergunta

  • Boa tarde galera,

    sei que esse assunto ja foi muito abordado em diversos fóruns, vi alguns documentos do MVP (Gustavo Maia), mas estou com dificuldades para pegar os campos que preciso, segue meu código:

    DOC XML:

    <Events>

    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

    <System>

    <Provider Name="Microsoft-Windows-CAPI2" Guid="{5bbca4a8-b209-48dc-a8c7-b23d3e5216fb}" EventSourceName="Microsoft-Windows-CAPI2" />

    <EventID Qualifiers="0">4107</EventID>

    <Version>0</Version>

    <Level>2</Level>

    <Task>0</Task>

    <Opcode>0</Opcode>

    <Keywords>0x8080000000000000</Keywords>

    <TimeCreated SystemTime="2011-06-26T19:55:08.797304200Z" />

    <EventRecordID>13059</EventRecordID>

    <Correlation ActivityID="{00000100-0000-0000-30F6-10060A2ACC01}" />

    <Execution ProcessID="152" ThreadID="2644" />

    <Channel>Application</Channel>

    <Computer>BMTLSPDESV.belmetal.local</Computer>

    <Security />

    </System>

    <EventData>

    <Data>http://www.download.windowsupdate.com/msdownload/update/v3/static/trustedr/en/authrootstl.cab</Data>

    <Data>The data is invalid.</Data>

    </EventData>

    </Event>

    </Events>

    Cód. para leitura do XML:

    DECLARE

    @x_XML XML

    --(xsdEventLogs)

    SET

    @x_XML =

    (

     

    SELECT CAST(BulkColumn AS XML)

     

    FROM

     

    OPENROWSET(BULK N'C:\Logs_BD2.xml', SINGLE_BLOB) AS Arquivo

     

    )

    --SET ARITHABORT ON;

    SELECT

    Event.System.value('./@Version','numeric') AS

    Version

    FROM

    @x_XML.nodes('/Event/System') AS Event(System)

    Obrigado pela atenção

    quarta-feira, 29 de junho de 2011 20:17

Respostas

  • Boa Tarde,

    Named spaces podem dar um certo trabalho, mas é só declará-los que aí tá tudo certo. Adicionalmente abandone métodos como sp_xml_preparedocument. São inferiores a outros métodos como value, nodes, etc. Tente o seguinte:

    DECLARE @x_XML XML 
    SET @x_XML = 
    '<Events> 
    <Event xmlns:xsi="http://schemas.microsoft.com/win/2004/08/events/event">
    <System>
    <Provider Name="Microsoft-Windows-CAPI2" Guid="{5bbca4a8-b209-48dc-a8c7-b23d3e5216fb}" EventSourceName="Microsoft-Windows-CAPI2" />
    <EventID Qualifiers="0">4107</EventID>
    <Version>0</Version>
    <Level>2</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8080000000000000</Keywords>
    <TimeCreated SystemTime="2011-06-26T19:55:08.797304200Z" />
    <EventRecordID>13059</EventRecordID>
    <Correlation ActivityID="{00000100-0000-0000-30F6-10060A2ACC01}" />
    <Execution ProcessID="152" ThreadID="2644" />
    <Channel>Application</Channel>
    <Computer>BMTLSPDESV.belmetal.local</Computer>
    <Security />
    </System>
    <EventData>
    <Data>http://www.download.windowsupdate.com/msdownload/update/v3/static/trustedr/en/authrootstl.cab</Data>
    <Data>The data is invalid.
    </Data>
    </EventData>
    </Event>
    </Events>'
     
    ;WITH XMLNAMESPACES('http://schemas.microsoft.com/win/2004/08/events/event' as xsi)
    SELECT
     @x_XML.value('(/Events/Event/System/Provider/@Name)[1]','VARCHAR(50)') As Name,
     @x_XML.value('(/Events/Event/System/EventID/@Qualifiers)[1]','INT') As EventIDQualifiers,
     @x_XML.value('(/Events/Event/System/EventID)[1]','INT') As Qualifiers,
     @x_XML.value('(/Events/Event/System/TimeCreated/@SystemTime)[1]','DATETIME') As SystemTime,
     @x_XML.value('(/Events/Event/System/Computer)[1]','VARCHAR(50)') As Computer
    


    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar quinta-feira, 30 de junho de 2011 17:34
    • Marcado como Resposta Andre_Bel quarta-feira, 6 de julho de 2011 13:59
    quinta-feira, 30 de junho de 2011 17:33

Todas as Respostas

  • Andre,

     

    Quais campos voce quer ler?


    Fabrizzio A. Caputo
    Certificações: Oracle OCA 11g, MCTS SQL Server 2008 Implementation and Maintenance
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    quarta-feira, 29 de junho de 2011 20:38
    Moderador
  • Seriam esses,

    Provider Name="Microsoft-Windows-CAPI2"

    EventID Qualifiers="0"

    TimeCreated SystemTime

    Computer

     

    quarta-feira, 29 de junho de 2011 20:42
  • Fabrizzio,

    Fiz dessa forma, porém não esta retornando nenhum valor...

    DECLARE

    @x_XML XML

    --ENTRADA

     

    ,@x_XMLS INTEGER --SADA

    SET

    @x_XML =

    (

     

    '<Events>

    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">

    <System>

    <Provider Name="Microsoft-Windows-CAPI2" Guid="{5bbca4a8-b209-48dc-a8c7-b23d3e5216fb}" EventSourceName="Microsoft-Windows-CAPI2" />

    <EventID Qualifiers="0">4107</EventID>

    <Version>0</Version>

    <Level>2</Level>

    <Task>0</Task>

    <Opcode>0</Opcode>

    <Keywords>0x8080000000000000</Keywords>

    <TimeCreated SystemTime="2011-06-26T19:55:08.797304200Z" />

    <EventRecordID>13059</EventRecordID>

    <Correlation ActivityID="{00000100-0000-0000-30F6-10060A2ACC01}" />

    <Execution ProcessID="152" ThreadID="2644" />

    <Channel>Application</Channel>

    <Computer>BMTLSPDESV.belmetal.local</Computer>

    <Security />

    </System>

    <EventData>

    <Data>http://www.download.windowsupdate.com/msdownload/update/v3/static/trustedr/en/authrootstl.cab</Data>

    <Data>The data is invalid.

    </Data>

    </EventData>

    </Event>

    </Events>'

     

    )

    PRINT CONVERT(VARCHAR(MAX),@x_XML

    )

    EXEC

    sp_xml_preparedocument @x_XMLS OUTPUT, @x_XML

    SELECT

    *FROM OPENXML(@x_XMLS, '/Events'

    )

     

    WITH (Name CHAR(200) '/Event/System/@Name',

     

    Computer char(200)'/Event/System/@Computer')

    EXEC

    sp_xml_removedocument @x_XMLS

    quinta-feira, 30 de junho de 2011 14:41
  • Boa Tarde,

    Named spaces podem dar um certo trabalho, mas é só declará-los que aí tá tudo certo. Adicionalmente abandone métodos como sp_xml_preparedocument. São inferiores a outros métodos como value, nodes, etc. Tente o seguinte:

    DECLARE @x_XML XML 
    SET @x_XML = 
    '<Events> 
    <Event xmlns:xsi="http://schemas.microsoft.com/win/2004/08/events/event">
    <System>
    <Provider Name="Microsoft-Windows-CAPI2" Guid="{5bbca4a8-b209-48dc-a8c7-b23d3e5216fb}" EventSourceName="Microsoft-Windows-CAPI2" />
    <EventID Qualifiers="0">4107</EventID>
    <Version>0</Version>
    <Level>2</Level>
    <Task>0</Task>
    <Opcode>0</Opcode>
    <Keywords>0x8080000000000000</Keywords>
    <TimeCreated SystemTime="2011-06-26T19:55:08.797304200Z" />
    <EventRecordID>13059</EventRecordID>
    <Correlation ActivityID="{00000100-0000-0000-30F6-10060A2ACC01}" />
    <Execution ProcessID="152" ThreadID="2644" />
    <Channel>Application</Channel>
    <Computer>BMTLSPDESV.belmetal.local</Computer>
    <Security />
    </System>
    <EventData>
    <Data>http://www.download.windowsupdate.com/msdownload/update/v3/static/trustedr/en/authrootstl.cab</Data>
    <Data>The data is invalid.
    </Data>
    </EventData>
    </Event>
    </Events>'
     
    ;WITH XMLNAMESPACES('http://schemas.microsoft.com/win/2004/08/events/event' as xsi)
    SELECT
     @x_XML.value('(/Events/Event/System/Provider/@Name)[1]','VARCHAR(50)') As Name,
     @x_XML.value('(/Events/Event/System/EventID/@Qualifiers)[1]','INT') As EventIDQualifiers,
     @x_XML.value('(/Events/Event/System/EventID)[1]','INT') As Qualifiers,
     @x_XML.value('(/Events/Event/System/TimeCreated/@SystemTime)[1]','DATETIME') As SystemTime,
     @x_XML.value('(/Events/Event/System/Computer)[1]','VARCHAR(50)') As Computer
    


    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.wordpress.com


    Classifique as respostas. O seu feedback é imprescindível
    • Sugerido como Resposta Gustavo Maia Aguiar quinta-feira, 30 de junho de 2011 17:34
    • Marcado como Resposta Andre_Bel quarta-feira, 6 de julho de 2011 13:59
    quinta-feira, 30 de junho de 2011 17:33