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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 XML importing / conversion difficulties
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

merlinblack
Starting Member

5 Posts

Posted - 04/07/2011 :  23:55:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 04/08/2011 :  13:11:26  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

merlinblack
Starting Member

5 Posts

Posted - 04/08/2011 :  13:47:51  Show Profile  Reply with Quote
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.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 04/08/2011 :  14:36:05  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/08/2011 :  14:41:25  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 04/08/2011 14:41:50
Go to Top of Page

merlinblack
Starting Member

5 Posts

Posted - 04/08/2011 :  17:19:00  Show Profile  Reply with Quote
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..


Go to Top of Page

merlinblack
Starting Member

5 Posts

Posted - 04/08/2011 :  17:19:25  Show Profile  Reply with Quote
Ideally the data would appear in a row like this:

Product Name, License Key, Product ID, Auth Key, User
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/08/2011 :  17:53:18  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 04/09/2011 20:59:42
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 04/08/2011 :  19:02:34  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

merlinblack
Starting Member

5 Posts

Posted - 04/10/2011 :  22:27:37  Show Profile  Reply with Quote
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!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/11/2011 :  15:22:52  Show Profile  Reply with Quote
You are quite welcome. Just be worried about performance if you have large amounts of data.
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.09 seconds. Powered By: Snitz Forums 2000