XML importing / conversion difficulties

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

SQL Server 2008 Forums

Transact-SQL (2008)

XML importing / conversion difficulties

Author

Topic

merlinblack
Starting Member

5 Posts

Posted - 2011-04-07 : 23:55:26

Hi,

I've a fair bit of experience with SQL Server, but new with XML, and this seems to be stumping me...

I have XML files that I need to parse and import into tables. The source XML looks like this:

<Root>
<Key>
<Data name="Computer" value="Computer21" />
<Data name="Product Name " value="Microsoft Internet explorer " />
<Data name="License Key" value="BBBBB-BBBBB-BBBBB-BBBBB-BBBBB" />
<Data name="Product ID" value="00000-000-0000000-00000" />
<Data name="Auth. Key" value="N/A" />
<Data name="User" value="N/A" />
<Data name="Company" value="N/A" />
</Key>
<Key>
<Data name="Computer" value="Computer21" />
<Data name="Product Name " value="Windows 7 Enterprise N " />
<Data name="License Key" value="BBBBB-BBBBB-BBBBB-BBBBB-BBBBB" />
<Data name="Product ID" value="00000-000-0000000-00000" />
<Data name="Auth. Key" value="N/A" />
<Data name="User" value="administrator" />
<Data name="Company" value="N/A" />
</Key>
</Root>

In this scenario one computer has one file, and I'll be dealing with numerous files over time, with varying numbers of software packages installed

I've imported the XML file into a staging table using openrowset/fileimport.

I'd like to import this into tables like this:
-------
Computer
ComputerID int pk
Name varchar(255)
--------
Software
SoftwareID int pk
ComputerID int fk
Product_Name varchar(255)
License_Key varchar(255)
Product_ID varchar(255)
Auth_Key varchar(255)

--------

The outcome of this would be that I'd create a new computer record based on the provided computer, then populate the software table with information derived from the rest of the information

The problem I'm basically running into is the fact that the XML isn't numbered or ...what I'd normally consider structured, just name, value, name, value...

I got as far as


declare @xml xml

select @xml = xml_data from xmlimporttest

SELECT
  [Node].value('@name','varchar(255)') AS ColumnName,
  [Node].value('@value','varchar(255)') AS Value
FROM @xml.nodes('RecoverKeys/Key/Data') Software([Node])

This gives me the data, but I can't really do a whole lot with it as it's not in any sort of meaningful set, nor can I pivot it due to there not being anything I can use a function on and still have a set of data...

From examples I've read it seems like this should work, but it's just returning a blank row:


declare @xml xml
select @xml = xml_data from xmlimporttest

SELECT
  [Node].value('@name','varchar(255)') AS ColumnName,
  [Node].value('@value','varchar(255)') AS Value
FROM @xml.nodes('RecoverKeys/Key/Data') Software([Node])
WHERE  @xml.exist ('RecoverKeys/Key/Data[@name = Computer]') = 1

So....I can't seem to wrap my head around this one.

Any help would be greatly appreciated.

Thanks!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 13:11:26

you have been able to import the data to staging? you mean the xml structure itself or the data in the xml?

If you don't have the passion to help people, you have no passion

merlinblack
Starting Member

5 Posts

Posted - 2011-04-08 : 13:47:51

I just imported it to a table (xmlimporttest), with a column named xml_data, type of xml.
So it's still the same thing that was in the source file, just imported to the table unmodified.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 14:36:05

do you have any control on these xml files? can you change them. there is one issue with the name="Computer" tag

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 14:41:25

Do you have an identifying information for each Key element? If there is - is it the Product Name? Assuming it is the Product Name, you could do something like this to pick out the identifier and put it together with each row?

SELECT
   N.value('@value','varchar(255)') as ProductName,
  [Node].value('@name','varchar(255)') AS ColumnName,
  [Node].value('@value','varchar(255)') AS Value
FROM 
  @xml.nodes('RecoverKeys/Key/Data') S2(N) 
  cross apply @xml.nodes('RecoverKeys/Key/Data') Software([Node])
where
  N.value('@name','varchar(255)') = 'Product Name '

Of course, you will still need to do more work to pivot the output of this query and insert into your destination tables etc.

merlinblack
Starting Member

5 Posts

Posted - 2011-04-08 : 17:19:00

No, I don't really have control over the XML files. It's fixed output from an application that we need to massage as little as possible.

Thanks for that try Sunitabeck,
That brought things...closer! But...the result set ended up a cartesian set (Internet Explorer with every license key, product id, etc. of every software installed, windows 7 for every license key, product id...etc etc..

merlinblack
Starting Member

5 Posts

Posted - 2011-04-08 : 17:19:25

Ideally the data would appear in a row like this:

Product Name, License Key, Product ID, Auth Key, User

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 17:53:18

If your data is not well-defined - i.e., there isn't any attribute that you can use to uniquely distinguish one Key from the next, use a query like this that picks up whatever is available:

SELECT
  [Node].query('fn:data(./Data[@name="Product Name "]/@value)[1]')  ProductName,
  [Node].query('fn:data(./Data[@name="License Key"]/@value)[1]')  LicenseKey,
  [Node].query('fn:data(./Data[@name="Product ID"]/@value)[1]')  ProductID,
  [Node].query('fn:data(./Data[@name="Auth. Key"]/@value)[1]')  AuthKey,
  [Node].query('fn:data(./Data[@name="User"]/@value)[1]')  UserName
FROM 
  @xml.nodes('RecoverKeys/Key') Software([Node])

It will pick up the element if it exists, if not it just have empty string for that column. If you have a large amounts of data in the table (each row being very large) you may want to add at least a primary XML index on the column.

Edit: I wrote this initially using a FLWOR expression, but the path expression above is simpler even though both may translate to the same query plan.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-08 : 19:02:34

the issue at the heart of this problem is that you have a not well defined xml

Computer infor should be with top xml


<Key name="Computer" value="Computer21" >
<Data name="Product Name " value="Microsoft Internet explorer " />
<Data name="License Key" value="BBBBB-BBBBB-BBBBB-BBBBB-BBBBB" />
<Data name="Product ID" value="00000-000-0000000-00000" />
<Data name="Auth. Key" value="N/A" />
<Data name="User" value="N/A" />
<Data name="Company" value="N/A" />
</Key>

then it will be easier to do it

If you don't have the passion to help people, you have no passion

merlinblack
Starting Member

5 Posts

Posted - 2011-04-10 : 22:27:37

yosiasz - Yep, definitely realize the XML sucks. Facepalm as soon as I saw it....Unfortunately it is what it is. I was just asked to take the data that another group is providing and get it into the database so we can aggregate and report on it.

sunitabeck - I think that will accomplish exactly what I need. You're a genius! Many many thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 15:22:52

You are quite welcome. Just be worried about performance if you have large amounts of data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources