SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Import XML into multiple tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Galius_Persnickety
Starting Member

USA
10 Posts

Posted - 02/11/2009 :  15:59:26  Show Profile  Reply with Quote
Hi,

I'm using SQLXMLBulkLoad in VBS to load small XML files into my SQL 2005 DB.

Each XML file has a small header that identifies the system it was generated on, its IP address etc. The rest of the file is a bunch of records.

I've been able to import the records, but I need to keep track of which system they came from (thus identifying specifically which XML file the records were imported from).

I assume I want to load the system identifying fields into one table, and link them to the actual records which will reside in a separate table.

What's the best way to do this? Is it defined in the XML schema file? How do I link each record to the 'source' identifier?

Thanks in advance for you patience and advice.

Gabe

SwePeso
Patron Saint of Lost Yaks

Sweden
30206 Posts

Posted - 02/11/2009 :  16:53:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Post the xml data and explain which elements and attributes should go where.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Galius_Persnickety
Starting Member

USA
10 Posts

Posted - 02/11/2009 :  17:19:51  Show Profile  Reply with Quote
The actual data is security sensitive, but here's an idea of the XML file format:

Details Application Version
Bob's Computer
192.168.1.3
00:cb:43:82:96
Excel v2.3
Photoshop v.11
Outlook 2008

So the first column is populated for the first couple of rows, then the rest of the rows contain data (while the first row is blank).
I need to ensure that my table containing the the first column points to all the rows in the table containing the other columns, so I know that the record containing 'Excel v2.3' is from Bob's Computer.

Does that make sense?



Gabe
Go to Top of Page

Galius_Persnickety
Starting Member

USA
10 Posts

Posted - 02/11/2009 :  17:20:22  Show Profile  Reply with Quote
Dang it, that was supposed to look like columns of data.

Gabe
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30206 Posts

Posted - 02/11/2009 :  17:23:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think XML files has elements...

<Root>
<ElementName>value</ElementName>
</Root>



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Galius_Persnickety
Starting Member

USA
10 Posts

Posted - 02/12/2009 :  17:03:37  Show Profile  Reply with Quote
Here's a sample XML file. Notice that some elements show in the beginning, but are not part of the regular records. I'd like all the records in the file to be linked to these unique fields (ASSET_ID, MAC ADDRESS) so I can find them based on the file they came from.



<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type='text/xsl' href='VMS6_Display.xslt'?>
<IMPORT_FILE xmlns="urn:FindingImport"><AUTHENTICATED>TRUE</AUTHENTICATED><ASSET><ASSET_ID TYPE="HOST NAME">z001xp1.zone1</ASSET_ID><ASSET_ID TYPE="ASSET NAME">z001xp1.zone1</ASSET_ID><ASSET_ID TYPE="IP ADDRESS">10.0.0.26</ASSET_ID><ASSET_ID TYPE="MAC ADDRESS">00:09:55:b1:5A:20</ASSET_ID><CONF_LEVEL><CONF_KEY>3</CONF_KEY></CONF_LEVEL><WORKSTATION>true</WORKSTATION><ELEMENT><ELEMENT_KEY>2</ELEMENT_KEY><ELEMENT_DESCRIP>Computing</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>5</ELEMENT_KEY><ELEMENT_DESCRIP>Operating System</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>51</ELEMENT_KEY><ELEMENT_DESCRIP>Windows</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>59</ELEMENT_KEY><ELEMENT_DESCRIP>Windows XP</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>62</ELEMENT_KEY><ELEMENT_DESCRIP>Windows XP SP2</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>124</ELEMENT_KEY><ELEMENT_DESCRIP>Application</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>203</ELEMENT_KEY><ELEMENT_DESCRIP>Browsers</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>221</ELEMENT_KEY><ELEMENT_DESCRIP>Role</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>224</ELEMENT_KEY><ELEMENT_DESCRIP>Workstation</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>631</ELEMENT_KEY><ELEMENT_DESCRIP>Internet Explorer</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>632</ELEMENT_KEY><ELEMENT_DESCRIP>IE Version 6</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>635</ELEMENT_KEY><ELEMENT_DESCRIP>IE Version 6 Service Pack 2</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>806</ELEMENT_KEY><ELEMENT_DESCRIP>Desktop Application - General</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>807</ELEMENT_KEY><ELEMENT_DESCRIP>Desktop Application - Remote Access</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>1087</ELEMENT_KEY><ELEMENT_DESCRIP>AntiSpyware</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>1095</ELEMENT_KEY><ELEMENT_DESCRIP>Generic AntiSpyware</ELEMENT_DESCRIP></ELEMENT><ELEMENT><ELEMENT_KEY>1433</ELEMENT_KEY><ELEMENT_DESCRIP>Microsoft Messenger 4.7</ELEMENT_DESCRIP></ELEMENT><ASSET_TOOL>GD</ASSET_TOOL><ASSET_TOOL_VERSION>2.0.8.8</ASSET_TOOL_VERSION><TARGET><TARGET_KEY>59</TARGET_KEY><TARGET_DESCRIP>Windows XP</TARGET_DESCRIP><FINDING><FINDING_ID TYPE="VK">V0001070</FINDING_ID><FINDING_STATUS>NR</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>Physical security</GD_VUL_NAME><GD_SEVERITY>2</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001072</FINDING_ID><FINDING_STATUS>NR</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>Shared User Accounts</GD_VUL_NAME><GD_SEVERITY>2</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001073</FINDING_ID><FINDING_STATUS>NF</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>Approved Service Packs</GD_VUL_NAME><GD_SEVERITY>2</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001074</FINDING_ID><FINDING_STATUS>O</FINDING_STATUS><FINDING_DETAILS OVERRIDE="O">Antivirus (Element ID: 604) is not present on the system.
</FINDING_DETAILS><SCRIPT_RESULTS>Antivirus (Element ID: 604) is not present on the system.
</SCRIPT_RESULTS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>Approved DOD Virus Scan Program</GD_VUL_NAME><GD_SEVERITY>1</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001075</FINDING_ID><FINDING_STATUS>NF</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>Display Shutdown Button</GD_VUL_NAME><GD_SEVERITY>3</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001076</FINDING_ID><FINDING_STATUS>NR</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>System Recovery Backups</GD_VUL_NAME><GD_SEVERITY>3</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001077</FINDING_ID><FINDING_STATUS>O</FINDING_STATUS><FINDING_DETAILS OVERRIDE="O">The following files have incorrect permissions: CHK(90000)
C:\WINDOWS\SYSTEM32\CONFIG\SecEvent.Evt

The following files have incorrect permissions: CHK(90001)
C:\WINDOWS\SYSTEM32\CONFIG\SysEvent.Evt

The following files have incorrect permissions: CHK(90002)
C:\WINDOWS\SYSTEM32\CONFIG\AppEvent.Evt

</FINDING_DETAILS><SCRIPT_RESULTS>The following files have incorrect permissions: CHK(90000)
C:\WINDOWS\SYSTEM32\CONFIG\SecEvent.Evt

The following files have incorrect permissions: CHK(90001)
C:\WINDOWS\SYSTEM32\CONFIG\SysEvent.Evt

The following files have incorrect permissions: CHK(90002)
C:\WINDOWS\SYSTEM32\CONFIG\AppEvent.Evt

</SCRIPT_RESULTS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>Incorrect ACLs for event logs</GD_VUL_NAME><GD_SEVERITY>2</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0001080</FINDING_ID><FINDING_STATUS>O</FINDING_STATUS><FINDING_DETAILS OVERRIDE="O">The following files have incorrect audit settings: CHK(90077)
C:C:\AUTOEXEC.BAT
C:\boot.ini
C:\CONFIG.SYS
C:\Documents and Settings
C:\drivers
C:\IO.SYS
C:\MSDOS.SYS
C:\NTDETECT.COM
C:\ntldr
C:\Program Files
C:\RECYCLER
C:\WINDOWS

System Auditing is not enabled.

</FINDING_DETAILS>configured to maintain log</GD_VUL_NAME><GD_SEVERITY>3</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0014713</FINDING_ID><FINDING_STATUS>NR</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>DTSG016-Antispyware logs are not be reviewed</GD_VUL_NAME><GD_SEVERITY>3</GD_SEVERITY></FINDING><FINDING><FINDING_ID TYPE="VK">V0014714</FINDING_ID><FINDING_STATUS>NR</FINDING_STATUS><TOOL>GD</TOOL><TOOL_VERSION>2.0.8.8</TOOL_VERSION><AUTHENTICATED_FINDING>TRUE</AUTHENTICATED_FINDING><GD_VUL_NAME>DTSG017-Antispyware included in incident response</GD_VUL_NAME><GD_SEVERITY>3</GD_SEVERITY></FINDING></TARGET></ASSET></IMPORT_FILE>


Gabe
Go to Top of Page

kemet45
Starting Member

1 Posts

Posted - 02/15/2012 :  13:56:30  Show Profile  Reply with Quote
Hi I need to backup my SQL server's data base using and online software that cannot upload files bigger than 5GB. My DB size is 190GB and I'm having issues to upload files to the storage space that I have available with zenok. I would like to learn how to get a file in different tables with files not bigger than GB?

Is it Possible. ???
Go to Top of Page

robvolk
Most Valuable Yak

USA
15662 Posts

Posted - 02/16/2012 :  06:53:36  Show Profile  Visit robvolk's Homepage  Reply with Quote
Kemet, please start a new thread for your question.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000