April 9, 2009 at 6:29 am
Hi there,
I am wondering how to import an XML-file into a relational table with only the XML-file information. I found scripts on the Internet how to import XML-data and how next to parse this XML-data into a relational table. But, every script I have found defines the relational table AND the XML-file for a SELECT/INSERT statement.
But consider: everything you need to know is already in the XML-file. I'm thinking about a way to use that info to create a SELECT/INSERT statement for a table, and a virtual table at that. Take the attributes and the data from the XML-file and build/load the relational table from that.
I am sure somebody must have thought about that and build something, either a free script are a professional XML manipulating tool.
Somebody can point me there?
Greetz,
Hans Brouwer
April 9, 2009 at 9:09 am
Hello Hans,
If I'm getting you right you're asking for a tool that will take the nodes and create tables based on that and fill those tables with the columns with the appropriate data. Is that what you're after?
everything you need to know is already in the XML-file.
I think your assumption is not correct:
The XML file itself normally doesn't include any table related information (e.g. table name, column name, data type, default value, primary key definition a.s.o.). Those information are stored (if at all) in the xml schema definition.
So, what are you looking for?
A program that would create a table based on a given XML schema?
Or a program that would create a table based on xml data without schema information?
If the latter how would you handle the nest level? Would you expect to create one table per nest level including all primary and foreign key relations? Based on what criteria would you choose your primary key per nest level? How would you name your table?
In order to figure out what you're after it would really help if you could provide sample data and expected result (attached as txt file) together with a more detailed explanation what you're trying to do - or not to do.
April 10, 2009 at 7:51 am
Read about OPENXML on books online. You can query an XML document and simply write the results to a table, then look at the schema.
The code below is a slightly modified example of the code posted in Books Online. The original example populates pre-existing Customers and Orders tables. I added a line to turn the last query into a SELECT... INTO... to create the #temp table.
Good luck.
-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Customers')
WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@docHandle, N'//Orders')
WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT *
into #temp-- added by Bob H.
FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- display the temporary table created from XML
select * from #temp
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle
drop table #temp
drop table customers
drop table orders
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 10, 2009 at 7:59 am
I just re-read your question. Since you have the metadata that defines names, datatypes, and lengths for your output columns, you could parse those attributes to build a CREATE TABLE string and then execute the string with sp_executeSQL. I am unfamiliar with any utility for doing that, but perhaps others will be able to point you in the right direction.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 11, 2009 at 9:42 am
Imu,
You are right, an XML file does not necessarily represents the (complete) destinationtable, there are more issues. Still, what I am looking at is a way to use the info in XML to, say, a single virtual table with the attributes given in the XML file. I'm currently experimenting with the SSIS task XQuery Source(or something).
Bob, it's the 2nd answer which is spot-on.
Tnx for answering both of you.
Greetz,
Hans Brouwer
April 11, 2009 at 12:01 pm
Hi
I agree with Lutz. An XML file does not contain enough information to create tables from. You may need an XSD for this. As you say you are currently experimenting with SSIS this might be the right way. I don't know a good way to handle this with TSQL.
If you are looking for a professional tool to create DDL statements from XSDs, I think XML-Spy and Oxygen-XML should be able to handle this.
Greets
Flo
April 11, 2009 at 12:02 pm
Hi Bob!
Bob Hovious (4/10/2009)
Read about OPENXML on books online.
You should have a look to the new XML functions of XML data type. MS introduced the OPENXML in SSE2k; but they redefined the complete XML handling in SSE2k5 and so the OPENXML is marked as deprecated and will be removed in future version. 😉
Send you sunny greets from here!
Flo
PS : Nice animated gif! Do you create them by your self?
April 11, 2009 at 12:15 pm
Hi Flo:
No I don't create them, I use Google Image Search, and there are also websites that post collections of images. "Biohazard" seems to inspire a lot of people.
Thanks for the heads up on OpenXML being deprecated. I haven't had to do anything new with XML lately, but I'll do some reading.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply