October 29, 2007 at 11:24 pm
I have an XML File that contains a heirarchical set of data. There are 3 sets of data within this XML. Customer, Orders & OrderDetails. I also have the XSD Schema for it as well.
How do I Insert this XML data into an SQL 2005 database?
What I need to achieve is basically a Customer Table, the Orders Table for that Customer and the OrderDetails fro that Order.
I'm new to XML and I'm unsure on how to tackle this!
October 30, 2007 at 2:05 am
Hi there,
can you please post also a part of the xml file .. to see exactly the structure 🙂
Thanks,
Oana.
October 30, 2007 at 3:15 am
You can read in the xml document into an xml variable like:
declare @xml xml
SELECT @xml = CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK 'g:\alma.xml', SINGLE_BLOB) AS x
select @xml
and then process it and break it up into your tables using XQuery like
SELECT @xml.query('//a')
Regards,
Andras
October 30, 2007 at 6:23 am
Use the suggestion above, or if you're passing the XML in as a parameter use the OPENXML syntax
DECLARE @Handle int
EXEC sp_xml_preparedocument @Handle OUTPUT, @XmlParameter
INSERT INTO TABLE x
SELECT ...
FROM OPENXML (@DocHandle, '/ROOT/',1)
WITH (Col1 int,
Col2 varchar(50)
...)
EXEC sp_xml_removedocument @Handle
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2007 at 6:36 am
Hey Grant,
I think you are adding two more steps
than Andras Method.
Actually I followed the same way what you specify here in my
last project but Now I think Andras method looks easier than this.
Please correct me If I am Wrong.
Cheers!
Sandy.
--
October 30, 2007 at 6:53 am
Nah, neither method is "wrong". If you're opening the XML from a file, definiately do what Andras suggested. If you're receiving the XML from an application through a parameter, I'd do my method.
However, I could be off on that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2007 at 6:46 pm
Isn't Andras process a 2 step process as well?
What he is saying is to insert the XML file into a XML DataType Column in a Database and then Query the XML Column using XQUERY to extarct the data and place them into the appropriate tables?
How is that different from the other process mentioned other than the fact that it is not querying from an XML Column?
Rememebr that I'm just using SQL Server Management Studio for this & possibly SSIS with the XML Source Transformation.
There are 3 sets of data in this XML file.
Customer
Orders
OrderDetails
These 3 items are all seperate tables with RI within the Database.
October 31, 2007 at 3:19 am
Grant Fritchey (10/30/2007)
Nah, neither method is "wrong". If you're opening the XML from a file, definiately do what Andras suggested. If you're receiving the XML from an application through a parameter, I'd do my method.However, I could be off on that.
OPENXML is available in SQL Server 2000. If you are not yet on 2005, then this is a major advantage 🙂
XQuery was introduced in 2005. It can be more powerful than OPENXML, one particularly nice feature is that you can use it with CROSS APPLY.
Andras
October 31, 2007 at 4:48 am
Ok Thanks for that.
I'm using your method but I'm a little stumped as to how to retrieve the values such as CustomerID, OrderID, CompanyName etc by using xquery?
I had a look in BOL and xquery retrives the data as XML, i need to retrieve the data as data like so,
CustomerID CompanyName
1 XYZ
2 ABC
11 XYZABC
and then INSERT this data in the appropriate tables. In the example above, I would need to insert this data in the Customer Table.
October 31, 2007 at 5:51 am
Andras Belokosztolszki (10/31/2007)
Grant Fritchey (10/30/2007)
Nah, neither method is "wrong". If you're opening the XML from a file, definiately do what Andras suggested. If you're receiving the XML from an application through a parameter, I'd do my method.However, I could be off on that.
OPENXML is available in SQL Server 2000. If you are not yet on 2005, then this is a major advantage 🙂
XQuery was introduced in 2005. It can be more powerful than OPENXML, one particularly nice feature is that you can use it with CROSS APPLY.
Andras
I haven't explored that much yet. You can use Xquery from a parameter? I've got some reading to do.
Thanks,
Grant
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 31, 2007 at 5:52 am
Hey Andras,
OPENXML is available in SQL Server 2000. If you are not yet on 2005, then this is a major advantage
XQuery was introduced in 2005. It can be more powerful than OPENXML, one particularly nice feature is that you can use it with CROSS APPLY.
I am sure your point is correct,
I followed the XQuery rather than OpenXML
in SQL Server 2005.
Nice Andras,
Cheers!
Sandy.
--
October 31, 2007 at 5:56 am
Hey Grant,:)
XQuery is Better than OPENXML,
As far I know and its coming newly in SQL Server 2005,
This much I can say, because last time I used this
XQuery Script rather than OpenXML Concepts...
Cheers!
Sandy.
--
November 1, 2007 at 5:14 am
November 1, 2007 at 5:34 am
Trigger (10/29/2007)
I have an XML File that contains a heirarchical set of data. There are 3 sets of data within this XML. Customer, Orders & OrderDetails. I also have the XSD Schema for it as well.How do I Insert this XML data into an SQL 2005 database?
What I need to achieve is basically a Customer Table, the Orders Table for that Customer and the OrderDetails fro that Order.
I'm new to XML and I'm unsure on how to tackle this!
When you have xml file and schema than the fastest way to have data in your database is to write a vb script using SQLXMLBulkLoad object. Specially if it's a huge file!
November 1, 2007 at 4:15 pm
I was able to do a small scale insert using xquery. You should be able to mold the methodology to fit your needs. Note: the xml tags were messing the post up, so I changed them to brackets.
DECLARE @xml XML
SET @xml = '
[Customer]
[CustomerDetails CustomerID="1" CompanyName="Company1"/]
[CustomerDetails CustomerID="2" CompanyName="Company2"/]
[CustomerDetails CustomerID="3" CompanyName="Company3"/]
[CustomerDetails CustomerID="4" CompanyName="Company4"/]
[CustomerDetails CustomerID="5" CompanyName="Company5"/]
[CustomerDetails CustomerID="6" CompanyName="Company6"/]
[CustomerDetails CustomerID="7" CompanyName="Company7"/]
[CustomerDetails CustomerID="8" CompanyName="Company8"/]
[/Customer]'
Declare @Customer table
(
CustomerID int,
CompanyName varchar(50)
)
INSERT INTO @Customer (CustomerID,CompanyName)
SELECT
a.col.value('@CustomerID', 'INT'),
a.col.value('@CompanyName', 'varchar(50)')
FROM
@xml.nodes('/Customer/CustomerDetails') AS a(col) --need to have a table and a column name
--Where a.col.value('@CustomerID', 'INT') = 7 --you can set filter here
select * from @Customer
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply