| Author |
 Topic  |
|
|
merlinblack
Starting Member
5 Posts |
 Posted - 04/07/2011 : 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
Flowing Fount of Yak Knowledge
USA
1635 Posts |
Posted - 04/08/2011 : 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 - 04/08/2011 : 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
Flowing Fount of Yak Knowledge
USA
1635 Posts |
Posted - 04/08/2011 : 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
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 04/08/2011 : 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. |
Edited by - sunitabeck on 04/08/2011 14:41:50 |
|
|
|
merlinblack
Starting Member
5 Posts |
Posted - 04/08/2011 : 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 - 04/08/2011 : 17:19:25
|
Ideally the data would appear in a row like this:
Product Name, License Key, Product ID, Auth Key, User
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 04/08/2011 : 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. |
Edited by - sunitabeck on 04/09/2011 20:59:42 |
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1635 Posts |
Posted - 04/08/2011 : 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 - 04/10/2011 : 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
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 04/11/2011 : 15:22:52
|
| You are quite welcome. Just be worried about performance if you have large amounts of data. |
|
|
| |
Topic |
|
XML importing / conversion difficulties