sql server 2012 读取xml中的内容

[hfyanzi]

D盘根目前下有一eee.xml文件，文件内容如下，想要读取<System>中TimeCreated SystemTime <EventData>中<Data Name="TargetUserName">administrator</Data> 和 <Data Name="IpAddress">231.204.175.145</Data>

查询条件
'2015-1-1'<TimeCreated SystemTime<='2015-10-1' and TargetUserName='administrator'

希望得到结果如：
date username IpAddress
2015-09-23T00:16:45.998853400Z administrator 231.204.175.145




<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<Events>


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


-<System>

<Provider Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" Name="Microsoft-Windows-Security-Auditing"/>

<EventID>4648</EventID>

<Version>0</Version>

<Level>0</Level>

<Task>12544</Task>

<Opcode>0</Opcode>

<Keywords>0x8020000000000000</Keywords>

<TimeCreated SystemTime="2015-09-23T00:16:45.998853400Z"/>

<EventRecordID>197749</EventRecordID>

<Correlation/>

<Execution ThreadID="5264" ProcessID="648"/>

<Channel>Security</Channel>

<Computer>xxx.cn.xxx.net</Computer>

<Security/>

</System>


-<EventData>

<Data Name="SubjectUserSid">S-1-5-18</Data>

<Data Name="SubjectUserName">xxx$</Data>

<Data Name="SubjectDomainName">CN</Data>

<Data Name="SubjectLogonId">0x3e7</Data>

<Data Name="LogonGuid">{00000000-0000-0000-0000-000000000000}</Data>

<Data Name="TargetUserName">administrator</Data>

<Data Name="TargetDomainName">CN</Data>

<Data Name="TargetLogonGuid">{6CBCCD8F-0842-1BEA-0B50-337342DB4B8D}</Data>

<Data Name="TargetServerName">localhost</Data>

<Data Name="TargetInfo">localhost</Data>

<Data Name="ProcessId">0x1784</Data>

<Data Name="ProcessName">C:\Windows\System32\winlogon.exe</Data>

<Data Name="IpAddress">244.204.175.155</Data>

<Data Name="IpPort">0</Data>

</EventData>

</Event>


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


-<System>

<Provider Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" Name="Microsoft-Windows-Security-Auditing"/>

<EventID>4648</EventID>

<Version>0</Version>

<Level>0</Level>

<Task>12544</Task>

<Opcode>0</Opcode>

<Keywords>0x8020000000000000</Keywords>

<TimeCreated SystemTime="2015-09-23T00:16:43.530076200Z"/>

<EventRecordID>197736</EventRecordID>

<Correlation/>

<Execution ThreadID="4076" ProcessID="648"/>

<Channel>Security</Channel>

<Computer>xxx.cn.xxx.net</Computer>

<Security/>

</System>


-<EventData>

<Data Name="SubjectUserSid">S-1-5-18</Data>

<Data Name="SubjectUserName">xxx$</Data>

<Data Name="SubjectDomainName">CN</Data>

<Data Name="SubjectLogonId">0x3e7</Data>

<Data Name="LogonGuid">{00000000-0000-0000-0000-000000000000}</Data>

<Data Name="TargetUserName">DWM-3</Data>

<Data Name="TargetDomainName">Window Manager</Data>

<Data Name="TargetLogonGuid">{00000000-0000-0000-0000-000000000000}</Data>

<Data Name="TargetServerName">localhost</Data>

<Data Name="TargetInfo">localhost</Data>

<Data Name="ProcessId">0x1784</Data>

<Data Name="ProcessName">C:\Windows\System32\winlogon.exe</Data>

<Data Name="IpAddress">-</Data>

<Data Name="IpPort">-</Data>

</EventData>

</Event>

[天-笑]

你的XML 有问题，实在不想理，理好再写

[hfyanzi]

引用 1 楼 shmilywcd 的回复:

你的XML 有问题，实在不想理，理好再写

不好意思，少了一个</Events>。 <?xml version="1.0" encoding="UTF-8" standalone="true"?> -<Events> -<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> -<System> <Provider Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" Name="Microsoft-Windows-Security-Auditing"/> <EventID>4648</EventID> <Version>0</Version> <Level>0</Level> <Task>12544</Task> <Opcode>0</Opcode> <Keywords>0x8020000000000000</Keywords> <TimeCreated SystemTime="2015-09-23T00:16:45.998853400Z"/> <EventRecordID>197749</EventRecordID> <Correlation/> <Execution ThreadID="5264" ProcessID="648"/> <Channel>Security</Channel> <Computer>xxx.cn.xxx.net</Computer> <Security/> </System> -<EventData> <Data Name="SubjectUserSid">S-1-5-18</Data> <Data Name="SubjectUserName">xxx$</Data> <Data Name="SubjectDomainName">CN</Data> <Data Name="SubjectLogonId">0x3e7</Data> <Data Name="LogonGuid">{00000000-0000-0000-0000-000000000000}</Data> <Data Name="TargetUserName">administrator</Data> <Data Name="TargetDomainName">CN</Data> <Data Name="TargetLogonGuid">{6CBCCD8F-0842-1BEA-0B50-337342DB4B8D}</Data> <Data Name="TargetServerName">localhost</Data> <Data Name="TargetInfo">localhost</Data> <Data Name="ProcessId">0x1784</Data> <Data Name="ProcessName">C:\Windows\System32\winlogon.exe</Data> <Data Name="IpAddress">244.204.175.155</Data> <Data Name="IpPort">0</Data> </EventData> </Event> -<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> -<System> <Provider Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" Name="Microsoft-Windows-Security-Auditing"/> <EventID>4648</EventID> <Version>0</Version> <Level>0</Level> <Task>12544</Task> <Opcode>0</Opcode> <Keywords>0x8020000000000000</Keywords> <TimeCreated SystemTime="2015-09-23T00:16:43.530076200Z"/> <EventRecordID>197736</EventRecordID> <Correlation/> <Execution ThreadID="4076" ProcessID="648"/> <Channel>Security</Channel> <Computer>xxx.cn.xxx.net</Computer> <Security/> </System> -<EventData> <Data Name="SubjectUserSid">S-1-5-18</Data> <Data Name="SubjectUserName">xxx$</Data> <Data Name="SubjectDomainName">CN</Data> <Data Name="SubjectLogonId">0x3e7</Data> <Data Name="LogonGuid">{00000000-0000-0000-0000-000000000000}</Data> <Data Name="TargetUserName">DWM-3</Data> <Data Name="TargetDomainName">Window Manager</Data> <Data Name="TargetLogonGuid">{00000000-0000-0000-0000-000000000000}</Data> <Data Name="TargetServerName">localhost</Data> <Data Name="TargetInfo">localhost</Data> <Data Name="ProcessId">0x1784</Data> <Data Name="ProcessName">C:\Windows\System32\winlogon.exe</Data> <Data Name="IpAddress">-</Data> <Data Name="IpPort">-</Data> </EventData> </Event> </Events>