Add rows to SQL Server database from xml file

Question

I'm busy converting a website from xml file base to a SQL Server 2012 express database, now I've managed to get most of the tags from the xml to import into SQL Server without hassle

My issue now lies with a particular section of these xmls

This is the section I'm having issues with

<?xml version="1.0" encoding="utf-16"?>
<License xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Reference>00b2d4cf-699c-4427-bcf0-a0ca456654c2</Reference>
 <MacAddresses>
  <string>00:0D:87:FE:9A:21</string>
  <string>00:20:ED:14:47:64</string>
  <string>00:13:D3:11:7C:D2</string>
  <string>00:13:D3:11:7C:D8</string>
  <string>00:19:D1:04:12:98</string>
  <string>00:19:66:C0:7F:AF</string>
 </MacAddresses>
</License>

I have a reference for each license and the MAC Addresses that are assigned to that license.
Now i want it to import it into sql as follows:

Reference                               MACAddress
---------                               ----------
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:0D:87:FE:9A:21
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:20:ED:14:47:64
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:13:D3:11:7C:D2
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:13:D3:11:7C:D8
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:19:D1:04:12:98
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:19:D1:04:12:98
00b2d4cf-699c-4427-bcf0-a0ca456654c2    00:19:66:C0:7F:AF

I would do it manually but I have 700+ license to import and it would take far too long to do it manually.

help would be appreciated

Parse the XML with an XML parser and INSERT the resulting data. — Robert Harvey, May 20 '13 at 16:02
***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things like XML support are **highly** vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, May 20 '13 at 16:03
@marc_s: Do any of them have support for importing XML directly into a table? — Robert Harvey, May 20 '13 at 16:04
@RobertHarvey: SQL Server has native XQuery support, and I believe Oracle and DB2 have something, too (don't know these in detail) — marc_s, May 20 '13 at 16:05
Text editor, global search and replace `` to `INSERT INTO mytable VALUES('` and `` to `')` Then if you can replace with regular expressions, remove any blank line or line with `<` or `>` — David Starkey, May 20 '13 at 16:05
i have used a part vbscript to import the rest of the data into my tables... just this section that im stuck with:/ — NapalmKnight, May 20 '13 at 16:06

score 1 · Accepted Answer · answered May 20 '13 at 16:08

If you're using SQL Server 2005 or newer, you can use the native XQuery support to extract the data you want from that XML with this:

DECLARE @input XML = '<License xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Reference>00b2d4cf-699c-4427-bcf0-a0ca456654c2</Reference>
 <MacAddresses>
  <string>00:0D:87:FE:9A:21</string>
  <string>00:20:ED:14:47:64</string>
  <string>00:13:D3:11:7C:D2</string>
  <string>00:13:D3:11:7C:D8</string>
  <string>00:19:D1:04:12:98</string>
  <string>00:19:66:C0:7F:AF</string>
 </MacAddresses>
</License>'

SELECT
    Reference = License.value('(Reference)[1]', 'varchar(50)'),
    MacAddress = MacAddr.value('.', 'varchar(50)')
FROM
    @input.nodes('License') AS XTbl1(License)
CROSS APPLY
    License.nodes('MacAddresses/string') AS XTbl2(MacAddr)

Gives output of:

Reference                              MacAddress
00b2d4cf-699c-4427-bcf0-a0ca456654c2   00:0D:87:FE:9A:21
00b2d4cf-699c-4427-bcf0-a0ca456654c2   00:20:ED:14:47:64
00b2d4cf-699c-4427-bcf0-a0ca456654c2   00:13:D3:11:7C:D2
00b2d4cf-699c-4427-bcf0-a0ca456654c2   00:13:D3:11:7C:D8
00b2d4cf-699c-4427-bcf0-a0ca456654c2   00:19:D1:04:12:98
00b2d4cf-699c-4427-bcf0-a0ca456654c2   00:19:66:C0:7F:AF

And of course, you can also use an INSERT INTO dbo.YourTable(Reference, MacAddress) .... before the SELECT to automatically insert this data into the table

Add rows to SQL Server database from xml file

1 Answers1