Import XML into multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import XML into multiple tables

Author

Topic

Galius_Persnickety
Starting Member

10 Posts

Posted - 2009-02-11 : 15:59:26

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

30421 Posts

Posted - 2009-02-11 : 16:53:45

Post the xml data and explain which elements and attributes should go where.

E 12°55'05.63"
N 56°04'39.26"

Galius_Persnickety
Starting Member

10 Posts

Posted - 2009-02-11 : 17:19:51

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

Galius_Persnickety
Starting Member

10 Posts

Posted - 2009-02-11 : 17:20:22

Dang it, that was supposed to look like columns of data.

Gabe

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-11 : 17:23:52

I think XML files has elements...

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

E 12°55'05.63"
N 56°04'39.26"

Galius_Persnickety
Starting Member

10 Posts

Posted - 2009-02-12 : 17:03:37

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

kemet45
Starting Member

1 Post

Posted - 2012-02-15 : 13:56:30

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. ???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-02-16 : 06:53:36

Kemet, please start a new thread for your question.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources