sql - Read XML Root values into Table -
i working xml files save database table.
this xml:
<?xml version="1.0"encoding="utf-8"standalone="true"?> <computer name="wh7"processior="intel(r) core(tm)2 duo cpu p9600 @ 2.66ghz" manufacturer="dell inc." model="latitude e6500 domain="workgroup" ram="3536 mb" servicetag="dgqktj1"> <bios manufacturer="dell inc." version="phoenix rom bios plus version 1.10 a19"/> these properties go asset table <os name="microsoft windows 7 ultimate" servicepack="1"/> os - category, namem, service pack - property, <harddisks> harddisks - category, value - save name in property table <harddisk value=c:\ (ntfs) total space: 127.8373 gb free space: 16.2686 gb/> </harddisks> <networkcards> naetworkcards - category, adapter, ipaddress, subnet mask, macaddress, dhcpenable properties <network adapter="[00000007] intel(r) 82567lm gigabit network connection" ipaddress="10.5.99.48" subnet mask="255.255.255.0" macaddress="00:21:70:f7:c9:a2" dhcpenabled="true"/> <network adapter="[00000009] vmware virtual ethernet adapter vmnet1" ipaddress="192.168.216.1" subnet mask="255.255.255.0" macaddress="00:50:56:c0:00:01" dhcpenabled="false"/> <network adapter="[00000012] dell wireless 1397 wlan mini-card" ipaddress="192.168.1.64" subnet mask="255.255.255.0" macaddress="00:24:2c:63:f5:08" dhcpenabled="true"/> <network adapter="[00000013] vmware virtual ethernet adapter vmnet8" ipaddress="192.168.202.1" subnet mask="255.255.255.0" macaddress="00:50:56:c0:00:08" dhcpenabled="false"/> <network adapter="[00000020] cisco systems vpn adapter" ipaddress="172.31.3.251" subnet mask="255.255.252.0" macaddress="00:05:9a:3c:78:00" dhcpenabled="false"/> </networkcards> <graphicscards> graphicscards - category, card - property <graphics card="current display controller configuration"/> </graphicscards> <localgroups> <group name="administrators"> localgroups - category, name - property <member="administrator"/> member - category, administrator, whargrove - property, administrators - parent <member="whargrove"/> </group> <group name="backup operators"> </group> <group name="cryptographic operators"> </group> <group name="distributed com users"> </group> <group name="event log readers"> </group> <group name="guests"> <member="guest"/> </group> <group name="iis_iusrs"> localgroups - category, iis_users - property <member="iusr"/> localgroups - member, iuser - property, iis_users - parent </group> <group name="network configuration operators"> </group> <group name="performance log users"> </group> <group name="performance monitor users"> </group> <group name="power users"> </group> <group name="remote desktop users"> </group> <group name="replicator"> </group> <group name="users"> <member="interactive"/> <member="authenticated users"/> <member="ips_admin"/> </group> <group name="boinc_admins"> <member="administrators"/> <member="whargrove"/> </group> <group name="boinc_projects"> </group> <group name="boinc_users"> </group> <group name="homeusers"> <member="whargrove"/> <member="administrator"/> <member="homegroupuser$"/> </group> <group name="__vmware__"> <member="__vmware_user__"/> </group> </localgroups> <softwareinstalled> softwareinstalled - category, productname, versionstring, installlocation, installsource, installdate, publisher, localpackage - property <productname="vmware infrastructure update" versionstring="2.5.0.64237" installlocation="c:\program files\vmware\infrastructure\" installsource="c:\users\whargr~1\appdata\local\temp\_isa1fb\" installdate="20110721" publisher="vmware, inc." localpackage="c:\windows\installer\7124493c.msi"/> <productname="nt testing tcp tool" versionstring="1.0.0" installsource="c:\users\whargrove\downloads\" installdate="20130211" publisher="microsoft corporation" localpackage="c:\windows\installer\20788d8c.msi"/> <productname="inssider" versionstring="2.1.3" installsource="c:\users\whargrove\downloads\" installdate="20120605" publisher="metageek" localpackage="c:\windows\installer\8eb9c024.msi"/> <productname="messageops exporter" versionstring="1.20" installsource="c:\users\whargrove\appdata\local\temp\temp1_moexport1.2.zip\" installdate="20130409" publisher="messageops" localpackage="c:\windows\installer\55a29199.msi"/> <productname="aventail access manager" versionstring="10.53.52" installsource="c:\users\whargr~1\appdata\local\temp\" installdate="20111214" publisher="sonicwall inc" localpackage="c:\windows\installer\5139fce3.msi"/> <productname="humanconcepts orgplus 8 plug-in" versionstring="8.3.1195.0" installlocation="c:\program files\common files\humanconcepts\orgplus 8 plug-in\" installsource="c:\users\whargr~1\appdata\local\temp\{5ab92f13-75a0-4fcc-90fa-ac9f078ca828}\" installdate="20110506" publisher="humanconcepts" localpackage="c:\windows\installer\7f0e109.msi"/> <productname="google talk plugin" versionstring="4.1.3.13728" installsource="c:\users\whargrove\appdata\local\google\update\install\{baf66c66-be24-43fd-ba71-1652286b168a}\" installdate="20130627" publisher="google" localpackage="c:\windows\installer\4ec819ea.msi"/> <productname="microsoft office visio mui (english) 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-0054-0409-0000-0000000ff1ce}-c\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\32a4511.msi"/> <productname="microsoft office shared setup metadata mui (english) 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-0115-0409-0000-0000000ff1ce}-c\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\ad65c.msi"/> <productname="microsoft office proofing (english) 2007" versionstring="12.0.4518.1014" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-002c-0409-0000-0000000ff1ce}-c\" installdate="20100223" publisher="microsoft corporation" localpackage="c:\windows\installer\ad69a.msi"/> <productname="microsoft office shared mui (english) 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-0115-0409-0000-0000000ff1ce}-c\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\ad656.msi"/> <productname="microsoft office proof (english) 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-002c-0409-0000-0000000ff1ce}-c\proof.en\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\ad694.msi"/> <productname="microsoft office proof (spanish) 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-002c-0409-0000-0000000ff1ce}-c\proof.es\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\ad686.msi"/> <productname="microsoft office proof (french) 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90120000-002c-0409-0000-0000000ff1ce}-c\proof.fr\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\ad68d.msi"/> <productname="microsoft office visio professional 2007" versionstring="12.0.6612.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{91120000-0051-0000-0000-0000000ff1ce}-c\" installdate="20130701" publisher="microsoft corporation" localpackage="c:\windows\installer\32a454d.msi"/> <productname="microsoft application error reporting" versionstring="12.0.6012.5000" installsource="c:\program files\common files\windows live\.cache\ce1168531ccf8a006\" installdate="20120302" publisher="microsoft corporation" localpackage="c:\windows\installer\bdfec60a.msi"/> <productname="microsoft office professional plus 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0011-0000-0000-0000000ff1ce}-c\" installdate="20130701" publisher="microsoft corporation" localpackage="c:\windows\installer\2174b34.msi"/> <productname="microsoft office onenote mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-00a1-0409-0000-0000000ff1ce}-c\" installdate="20111108" publisher="microsoft corporation" localpackage="c:\windows\installer\2c0e4c29.msi"/> <productname="microsoft office infopath mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0044-0409-0000-0000000ff1ce}-c\" installdate="20110726" publisher="microsoft corporation" localpackage="c:\windows\installer\2173ff1.msi"/> <productname="microsoft office access mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0117-0409-0000-0000000ff1ce}-c\access.en-us\" installdate="20110726" publisher="microsoft corporation" localpackage="c:\windows\installer\2c0e4c33.msi"/> <productname="microsoft office shared setup metadata mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0115-0409-0000-0000000ff1ce}-c\" installdate="20110726" publisher="microsoft corporation" localpackage="c:\windows\installer\2c0e4bfc.msi"/> <productname="microsoft office excel mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0016-0409-0000-0000000ff1ce}-c\" installdate="20110726" publisher="microsoft corporation" localpackage="c:\windows\installer\2c0e4c09.msi"/> <productname="microsoft office access setup metadata mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0117-0409-0000-0000000ff1ce}-c\" installdate="20110726" publisher="microsoft corporation" localpackage="c:\windows\installer\2c0e4c38.msi"/> <productname="microsoft office powerpoint mui (english) 2010" versionstring="14.0.6029.1000" installlocation="c:\program files\microsoft office\" installsource="c:\msocache\all users\{90140000-0018-0409-0000-0000000ff1ce}-c\" installdate="20110726" publisher="microsoft corporation" localpackage="c:\windows\installer\2c0e4c03.msi"/> </softwareinstalled> </computer> and database table's columns
id category property value parent t-sql create script table:
create table assetproperty ( id int identity(100001, 1) not null, category varchar(120), property varchar(100), value varchar (max), parent varchar(50) ) i want save above xml data table.
from xml file os needs go "category" column , name needs go "property" column , name value should go "value" column , parent should "administrators" from
<localgroups> <group name="administrators">. how achieve this?
something this:
select t.c.value('local-name(.)', 'nvarchar(max)') category, a.c.value('local-name(.)', 'nvarchar(max)') property, a.c.value('.', 'nvarchar(max)') value @xml.nodes('//*') t(c) outer apply t.c.nodes('./@*') a(c)
Comments
Post a Comment