Importar arquivo xml

Question

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

Answer 1

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: [e-mail]

Answer 2

Seriam esses,

Provider Name="Microsoft-Windows-CAPI2"

EventID Qualifiers="0"

TimeCreated SystemTime

Computer

 

Answer 3

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

Answer 4

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