dkr0001
asked on
Import XML data into SQL Server (Asp.NET)
Hi,
I have an XML file for Products with product name, short_description, long_description.
I am not sure how to read through the nodes and grab the information into ASP.NET and update the Products table in SQL Server.
The XML file will have products that are already in the database for updating and also might be having new products to be inserted.
Can somebody help me out with this? Any example sites I can refer?
Thanks in advance.
Deepa
I have an XML file for Products with product name, short_description, long_description.
I am not sure how to read through the nodes and grab the information into ASP.NET and update the Products table in SQL Server.
The XML file will have products that are already in the database for updating and also might be having new products to be inserted.
Can somebody help me out with this? Any example sites I can refer?
Thanks in advance.
Deepa
Here's an alternate point of view.
While datasets can be nice to have around when the source XML is relational (tabular), if it's hierarchical it might be more trouble using datasets...If the XML is quite large, then it's going to be slow and resource intensive (see below).
Options 3 and 4 above are too much trouble; you have to handle transactions, concurrency, CRUD operations and any database constraints manually. Way too much work.
I disagree with Craig's assessment of option 5. SQL XML provides a lot of tools (and there are managed classes for .NET as well) for handling this. My favorite is the Updategram.
A) Updategrams will transactionally update the SQL Server, providing concurrency protection as well. They're pretty cool. I'm comfortable with XSLT, so I would probably write a transform that would package the XML document as an updategram, and send it to SQL Server that way. Updategrams handle database constraints for you, and they will automatically insert/update/delete a record depending on how they're constructed. If someone has changed the underlying data in the meantime (concurrency), you can have a particular update fail and roll back. In fact, you can ignore concurrency constrains for some columns, if you wish, and enforce it for others. The transaction boundary can be a single row, any combination of rows, or the entire update.
B) If you have a separate database team, they probably prefer to do everything in a stored procedure. I've never had much success convincing a database group to let me do all the work in the middle tier. ;^)
In that case, you can use OpenXML in a stored procedure to do the update--this is harder because you're writing the code in a sproc, and transact-SQL is a bizarre language. Of course, if you have the database team, *they'll* be writing the sproc, not you! ;^) But from the ASP.NET end, it's real easy--just a few lines of code:
/// <summary>
/// Helper function to update the database. Use SqlXmlCommand object and SqlXmlParameter object
/// to execute a sproc.
/// </summary>
/// <param name="xml">XML node representing the top level of the document fragment used to update
/// the Forms database. </param>
/// <returns>boolean</returns>
private bool UpdateDb(string xml)
{
SqlXmlParameter SourceDataXml;
try
{
SqlXmlCommand cmd = new SqlXmlCommand(DBConnString
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.CommandText = "Execute My_Stored_Procedure_sp ?";
SourceDataXml = cmd.CreateParameter();
SourceDataXml.Name = "source_data_xml";
SourceDataXml.Value = xml;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
EventLog.WriteEntry("My App", "Exception during ExecuteNonQuery...Update failed: " + ex.Message + "[" + ex.Source + "]" + ex.StackTrace, EventLogEntryType.Error);
throw new Exception("Insert failed: \r\n" + ex.Message);
}
}
catch(Exception ex)
{
EventLog.WriteEntry("My App", "Could not construct SqlXmlCommand: " + ex.Message + "[" + ex.Source + "]" + ex.StackTrace, EventLogEntryType.Error);
throw new Exception("Could not construct SqlXmlCommand: \r\n" + ex.Message + "[" + ex.Source + "]" + ex.StackTrace);
}
return true;
}
The stored procedure shreds the XML internally, and updates according to whatever rules and constraints you (or they) want. I've handled pretty complex XML structures this way, and everyone's happy. The update is in the sproc (compiled execution plan), and I still get to use my hierarchical XML.
If you like this approach, post a sample of your XML, and I post an example sproc using OpenXML to do updates and inserts.
C) If your XML document is quite large, then I would avoid .NET methods in any case--they're going to be too slow. Use the SQL XML bulk loader COM component; even calling it from ASP.NET will be faster than datasets or any other method on extremely large documents. You can use whatever database constraints you need as well, and it will also do CRUD updates (well, not reads, but all the others).
There are a number of other methods available as well, such as writing the SQL to do the updates in a SQL XML template, using SOAP and web services (also supported by SQL XML), etc. These are probably less useful in your case, but worth keeping in mind.
All of this is well documented in the help documentation that comes with the SQL XML 3.0 download. I use it all the time--very cool stuff. I don't have to restrict myself to datasets and flat tabular structures. If I'm going to have to write an annotated schema with a dataset to map hierarchical data to my Database, I'd just as soon use the SQL XML stuff anyway.
Regards,
Mike Sharp
While datasets can be nice to have around when the source XML is relational (tabular), if it's hierarchical it might be more trouble using datasets...If the XML is quite large, then it's going to be slow and resource intensive (see below).
Options 3 and 4 above are too much trouble; you have to handle transactions, concurrency, CRUD operations and any database constraints manually. Way too much work.
I disagree with Craig's assessment of option 5. SQL XML provides a lot of tools (and there are managed classes for .NET as well) for handling this. My favorite is the Updategram.
A) Updategrams will transactionally update the SQL Server, providing concurrency protection as well. They're pretty cool. I'm comfortable with XSLT, so I would probably write a transform that would package the XML document as an updategram, and send it to SQL Server that way. Updategrams handle database constraints for you, and they will automatically insert/update/delete a record depending on how they're constructed. If someone has changed the underlying data in the meantime (concurrency), you can have a particular update fail and roll back. In fact, you can ignore concurrency constrains for some columns, if you wish, and enforce it for others. The transaction boundary can be a single row, any combination of rows, or the entire update.
B) If you have a separate database team, they probably prefer to do everything in a stored procedure. I've never had much success convincing a database group to let me do all the work in the middle tier. ;^)
In that case, you can use OpenXML in a stored procedure to do the update--this is harder because you're writing the code in a sproc, and transact-SQL is a bizarre language. Of course, if you have the database team, *they'll* be writing the sproc, not you! ;^) But from the ASP.NET end, it's real easy--just a few lines of code:
/// <summary>
/// Helper function to update the database. Use SqlXmlCommand object and SqlXmlParameter object
/// to execute a sproc.
/// </summary>
/// <param name="xml">XML node representing the top level of the document fragment used to update
/// the Forms database. </param>
/// <returns>boolean</returns>
private bool UpdateDb(string xml)
{
SqlXmlParameter SourceDataXml;
try
{
SqlXmlCommand cmd = new SqlXmlCommand(DBConnString
cmd.CommandType = SqlXmlCommandType.Sql;
cmd.CommandText = "Execute My_Stored_Procedure_sp ?";
SourceDataXml = cmd.CreateParameter();
SourceDataXml.Name = "source_data_xml";
SourceDataXml.Value = xml;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
EventLog.WriteEntry("My App", "Exception during ExecuteNonQuery...Update failed: " + ex.Message + "[" + ex.Source + "]" + ex.StackTrace, EventLogEntryType.Error);
throw new Exception("Insert failed: \r\n" + ex.Message);
}
}
catch(Exception ex)
{
EventLog.WriteEntry("My App", "Could not construct SqlXmlCommand: " + ex.Message + "[" + ex.Source + "]" + ex.StackTrace, EventLogEntryType.Error);
throw new Exception("Could not construct SqlXmlCommand: \r\n" + ex.Message + "[" + ex.Source + "]" + ex.StackTrace);
}
return true;
}
The stored procedure shreds the XML internally, and updates according to whatever rules and constraints you (or they) want. I've handled pretty complex XML structures this way, and everyone's happy. The update is in the sproc (compiled execution plan), and I still get to use my hierarchical XML.
If you like this approach, post a sample of your XML, and I post an example sproc using OpenXML to do updates and inserts.
C) If your XML document is quite large, then I would avoid .NET methods in any case--they're going to be too slow. Use the SQL XML bulk loader COM component; even calling it from ASP.NET will be faster than datasets or any other method on extremely large documents. You can use whatever database constraints you need as well, and it will also do CRUD updates (well, not reads, but all the others).
There are a number of other methods available as well, such as writing the SQL to do the updates in a SQL XML template, using SOAP and web services (also supported by SQL XML), etc. These are probably less useful in your case, but worth keeping in mind.
All of this is well documented in the help documentation that comes with the SQL XML 3.0 download. I use it all the time--very cool stuff. I don't have to restrict myself to datasets and flat tabular structures. If I'm going to have to write an annotated schema with a dataset to map hierarchical data to my Database, I'd just as soon use the SQL XML stuff anyway.
Regards,
Mike Sharp
ASKER
Thanks for the quick response.
Since i am beginner to XML. I will have go through both the responses and try them and then assign points. Please be patient with me.
Thanks
Deepa
Since i am beginner to XML. I will have go through both the responses and try them and then assign points. Please be patient with me.
Thanks
Deepa
ASKER
Here is my page load code. Its simple.
..........................
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim ds As New DataSet
ds.ReadXml(Server.MapPath(
dgProducts.DataSource = ds
dgProducts.DataBind()
End Sub
..........................
and my XML for customers is customer.xml file
<?xml version="1.0" encoding="UTF-8" ?>
<customerData>
<customer id="WID10">
<companyInfo>
<nameInfo>
<companyName>Widgets Inc.</companyName>
<contactName name="John Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Anywhere" city="Pinetop" state="AZ" zip="85244" country="USA" />
<phone>
<busPhone busLine="520 123-1234" mobile="520 123-1238" />
<busFax busLine="520 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
<customer id="CarpA">
<companyInfo>
<nameInfo>
<companyName>Carpet Inc.</companyName>
<contactName name="Jane Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Red Carpet St." city="Phoenix" state="AZ" zip="85999" country="USA" />
<phone>
<busPhone busLine="480 123-1234" mobile="480 123-1238" />
<busFax busLine="480 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
</customerData>
..........................
When i run the page, the data grid is not getting binded properly.
Here is what I see inthe readxml.aspx,
customer_Id id
0 WID10
1 CarpA
Should'nt i get all the information in the grid?
Deepa
..........................
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim ds As New DataSet
ds.ReadXml(Server.MapPath(
dgProducts.DataSource = ds
dgProducts.DataBind()
End Sub
..........................
and my XML for customers is customer.xml file
<?xml version="1.0" encoding="UTF-8" ?>
<customerData>
<customer id="WID10">
<companyInfo>
<nameInfo>
<companyName>Widgets Inc.</companyName>
<contactName name="John Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Anywhere" city="Pinetop" state="AZ" zip="85244" country="USA" />
<phone>
<busPhone busLine="520 123-1234" mobile="520 123-1238" />
<busFax busLine="520 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
<customer id="CarpA">
<companyInfo>
<nameInfo>
<companyName>Carpet Inc.</companyName>
<contactName name="Jane Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Red Carpet St." city="Phoenix" state="AZ" zip="85999" country="USA" />
<phone>
<busPhone busLine="480 123-1234" mobile="480 123-1238" />
<busFax busLine="480 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
</customerData>
..........................
When i run the page, the data grid is not getting binded properly.
Here is what I see inthe readxml.aspx,
customer_Id id
0 WID10
1 CarpA
Should'nt i get all the information in the grid?
Deepa
The dataset does not see your XML as a single table, due to the way it is structured. To see how the dataset is interpreting your data, open the file in Visual Studio.NET and switch the view (see tab on bottom of document window) from XML to Data. The dataset creates nine interrelated tables, which is pretty wretched. That's the breaks with a mechanical interpretation of the structure.
As such, it won't bind automatically to a grid. Though you can select any of the nine tables by name and see the data subset in the grid.
dgProducts.DataSource = ds.tables("address")
To see a more detailed view of how the structure is interpreted, while the XML document window is open select XML -> Create Schema from the main menu bar. Ewww, very complicated stuff.
How many records will be included in a typical XML document? As rdcpro pointed out in his comment above, if you'll be handling large numbers of records you want to avoid loading the entire document into memory.
If you want to proces the XML in your code, and still want to do it through a dataset, you'll either have to suffer through the ugly schema or transorm it to a simple, flat structure. This is getting ridiculous, the amount of processing needed to accomplish this greatly exceeds the value. Do you have any control over in the incoming XML schema?
If you want to process the XML in your code and cannot change the schema to something better suited for a dataset, you'll have to go with the XmlDataReader option and loop through the elements.
Or, you can push the XML to SQL Server and do the work there, as rdcpro describes. Lots of options, and the key factor is how many records, how often, and where you prefer to do the programming.
-- Craig Yellick
As such, it won't bind automatically to a grid. Though you can select any of the nine tables by name and see the data subset in the grid.
dgProducts.DataSource = ds.tables("address")
To see a more detailed view of how the structure is interpreted, while the XML document window is open select XML -> Create Schema from the main menu bar. Ewww, very complicated stuff.
How many records will be included in a typical XML document? As rdcpro pointed out in his comment above, if you'll be handling large numbers of records you want to avoid loading the entire document into memory.
If you want to proces the XML in your code, and still want to do it through a dataset, you'll either have to suffer through the ugly schema or transorm it to a simple, flat structure. This is getting ridiculous, the amount of processing needed to accomplish this greatly exceeds the value. Do you have any control over in the incoming XML schema?
If you want to process the XML in your code and cannot change the schema to something better suited for a dataset, you'll have to go with the XmlDataReader option and loop through the elements.
Or, you can push the XML to SQL Server and do the work there, as rdcpro describes. Lots of options, and the key factor is how many records, how often, and where you prefer to do the programming.
-- Craig Yellick
ASKER
Hi Craig,
Probably 20-30 product records with product name, short_desc and long_desc will be included in the XML file.
I was able to bind the dataset to the datgrid but would like to see short and long desc as well.
This is what i see in the datagrid.
record_Id name
0 MS800.dcr
1 iR5000i
2 clc 5000
I am not sure if i am going in the write path to bind it in the datagrid and then a person manually checks it to make sure the information coming through is correct and then update it to the database. Is that possible?
How do I select short and long desc from the nodes among the rest of the nodes. I think I need an XML class :(
Deepa
Probably 20-30 product records with product name, short_desc and long_desc will be included in the XML file.
I was able to bind the dataset to the datgrid but would like to see short and long desc as well.
This is what i see in the datagrid.
record_Id name
0 MS800.dcr
1 iR5000i
2 clc 5000
I am not sure if i am going in the write path to bind it in the datagrid and then a person manually checks it to make sure the information coming through is correct and then update it to the database. Is that possible?
How do I select short and long desc from the nodes among the rest of the nodes. I think I need an XML class :(
Deepa
OK, 20-30 is not too many to process in your code. How often do you get the 20-30? Does this happen randomly all day long from multiple sources, or once per day from a single source, or something in between? This will help determine how much work you have to do as a programmer. Low volumes with low frequency, you can get away using power tools and the easy way out. High volumes or high frequency means you'll need to be way more careful with resources.
Simple answer on the XML-into-DataSet: it's a mess, and will never work easily. The XML schema is just too chunky, everything is elements within elements within elements, then attributes within an element for the address. As XML goes it's nothing special, but for use in a DataSet it's never going to work as-is. If you have any control over the XML schema, you should make it easier on yourself and make it more flat and consistent. When I mentioned using a dataset, it was just an option, without any idea of how your XML was structured, how much and how often.
Don't get fixated on displaying the data in a grid, that's not the goal of your process, right? You want to either insert or update records in SQL Server, and not display them in a grid. So the fact that there's no easy way to display selected fields of data is no big deal.
Finally, your sample XML is for customers while your initial question and follow-up comments talk about product info.
So... IF, and this is a big IF: if your product XML is strictly tabular, a dataset might (*might*) be an easy way to work with the XML. If it's not tabular then you'll have to abandon the use of a dataset and do something else, like XmlTextReader.
-- Craig Yellick
Simple answer on the XML-into-DataSet: it's a mess, and will never work easily. The XML schema is just too chunky, everything is elements within elements within elements, then attributes within an element for the address. As XML goes it's nothing special, but for use in a DataSet it's never going to work as-is. If you have any control over the XML schema, you should make it easier on yourself and make it more flat and consistent. When I mentioned using a dataset, it was just an option, without any idea of how your XML was structured, how much and how often.
Don't get fixated on displaying the data in a grid, that's not the goal of your process, right? You want to either insert or update records in SQL Server, and not display them in a grid. So the fact that there's no easy way to display selected fields of data is no big deal.
Finally, your sample XML is for customers while your initial question and follow-up comments talk about product info.
So... IF, and this is a big IF: if your product XML is strictly tabular, a dataset might (*might*) be an easy way to work with the XML. If it's not tabular then you'll have to abandon the use of a dataset and do something else, like XmlTextReader.
-- Craig Yellick
ASKER
OK, 20-30 is not too many to process in your code. How often do you get the 20-30? Does this happen randomly all day long from multiple sources, or once per day from a single source, or something in between?
>>>>[Deepa} We may get the XML feed once in 10 days or less. Depending on when a new product is released or updated.
want to either insert or update records in SQL Server, and not display them in a grid. So the fact that there's no easy way to display selected fields of data is no big deal.
>>>>[Deepa} Yes, my goal is to insert and update the records into SQL server.
I will go through the XMLTextReader in MSDN to understand how it works..
Thanks
Deepa
"The dataset does not see your XML as a single table, due to the way it is structured. To see how the dataset is interpreting your data, open the file in Visual Studio.NET and switch the view (see tab on bottom of document window) from XML to Data. The dataset creates nine interrelated tables, which is pretty wretched. That's the breaks with a mechanical interpretation of the structure."
This is why I like SQL XML...One shouldn't have to ask the source to provide a different XML schema. This is pretty easy using either OpenXML in a stored procedure, or an updategram with a transform.
Regards,
Mike Sharp
This is why I like SQL XML...One shouldn't have to ask the source to provide a different XML schema. This is pretty easy using either OpenXML in a stored procedure, or an updategram with a transform.
Regards,
Mike Sharp
This is what I really love about XML in general and .NET's XML support in particular, lots of ways to accomplish a task.
>> Pretty easy using OpenXML in a sproc or an updategram via transform
Yikes. Neither is "easy" by any definition of the word, especially so for someone who's never done it.
>> Shouldn't have to ask the source for a different schema
Agreed, but if the source is giving you wretched schema they could just as well take some constructive feedback and perhaps make your life easier. I've found that when I get called in to help with this sort of problem, most of the schema has been created on-the-fly by people who either don't know or care that their arbitrary design decisions are making all down-stream processing 10x more complicated than it needs to be. Even if using powerful concepts like OpenXML and XSL-T and updategrams, all of that is going to be much easier to create and maintain if the incoming XML is well structured.
In short, everything dkr0001 is trying to accomplish is made more complicated by that schema. Worst possible first-time experience with these technologies, whether DataSet or or XmlTextReader or updategram or OpenXML or whatever.
Worse, reading the XML is only the first step, dkr0001 still has to distinguish existing records from new records and react accordingly.
-- Craig Yellick
PS: Friendly exchange of ideas, right? Hope this isn't coming across wrong. Text messages are a bit short on personality.
>> Pretty easy using OpenXML in a sproc or an updategram via transform
Yikes. Neither is "easy" by any definition of the word, especially so for someone who's never done it.
>> Shouldn't have to ask the source for a different schema
Agreed, but if the source is giving you wretched schema they could just as well take some constructive feedback and perhaps make your life easier. I've found that when I get called in to help with this sort of problem, most of the schema has been created on-the-fly by people who either don't know or care that their arbitrary design decisions are making all down-stream processing 10x more complicated than it needs to be. Even if using powerful concepts like OpenXML and XSL-T and updategrams, all of that is going to be much easier to create and maintain if the incoming XML is well structured.
In short, everything dkr0001 is trying to accomplish is made more complicated by that schema. Worst possible first-time experience with these technologies, whether DataSet or or XmlTextReader or updategram or OpenXML or whatever.
Worse, reading the XML is only the first step, dkr0001 still has to distinguish existing records from new records and react accordingly.
-- Craig Yellick
PS: Friendly exchange of ideas, right? Hope this isn't coming across wrong. Text messages are a bit short on personality.
ASKER
I found a good example on this site.
http://www.topxml.com/xml/articles/sql2000/
I will try this and keep you guys posted.
Thanks for all ur help and suggestions. Will be back!(to assign points) :)
dkr
http://www.topxml.com/xml/articles/sql2000/
I will try this and keep you guys posted.
Thanks for all ur help and suggestions. Will be back!(to assign points) :)
dkr
Sorry, I just about had the Sproc written earlier but had to run off to a meeting. I haven't tested this, so there may be errors. I'll try to get a test run today to make sure there's no bugs, but this will take your XML and update the Northwind database in SQL Server. It will insert new records, if the customer doesn't exist, and it will update existing customers. Most of this is boilerplate stored procedure stuff. The only part specific to your application is the part in "shred the xml document"
Regards,
Mike Sharp
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
PRINT 'Creating stored procedure dbo.Insert_Update_Customer
GO
IF OBJECT_ID('dbo.Insert_Upda
DROP PROCEDURE dbo.Insert_Update_Customer
GO
CREATE PROCEDURE dbo.Insert_Update_Customer
@namespaceUri NVARCHAR(255)
,@source_data_xml NTEXT
)
AS
BEGIN
/*************************
-- PROCEDURE: dbo.Insert_Update_Customer
-- PURPOSE: Inserts or updates customer records based on source.xml
-- Information is passed in three parameters
-- the first parameter, @namespaceUri, contains an optional namespaceURI
-- (exclusive of the prefix used). This is needed to construct the
-- call to sp_xml_preparedocument so that QNames can be used in the
-- XPath expressions for OPENXML(). Currently not used.
-- @source_data_xml contains the data for the document itself.
-- no output results are needed, but success is returned.
-- INPUT:
-- ,@namespaceUri NVARCHAR(255) -- namespaceURI of the xml document
-- ,@source_data_xml NTEXT -- XML Document for operation
-- OUTPUT: None
-- CREATED:
-- DATE AUTHOR
-- 03/02/2005 Mike Sharp
-- UPDATED:
-- DATE AUTHOR
/*************************
SET NOCOUNT ON
/*************************
-- Declare Local Variables
/*************************
DECLARE @idoc INT
,@sql_error INT
,@return_code INT
,@error_message VARCHAR(1000)
,@stored_procedure_name SYSNAME
,@tran_count INT
,@Namespace_Root_Node NVARCHAR(300)
,@Last_Modified_Date DATETIME
,@Last_Modified_User NVARCHAR(128)
,@CustomerID NCHAR(5)
,@CompanyName NVARCHAR(40)
,@ContactName NVARCHAR(50)
,@ContactTitle NVARCHAR(50)
,@Address NVARCHAR(60)
,@City NVARCHAR(15)
,@Region NVARCHAR(15)
,@PostalCode NVARCHAR(10)
,@Country NVARCHAR(15)
,@Phone NVARCHAR(24)
,@Fax NVARCHAR(24)
/*************************
-- seed local variables
/*************************
SELECT @sql_error = 0
,@return_code = 0
,@stored_procedure_name = OBJECT_NAME(@@PROCID)
,@tran_count = @@TRANCOUNT
,@Namespace_Root_Node = '<customerData xmlns:my="' + @namespaceUri + '"/>'
-- Note that this implementation does not need namespaces.
-- This is how it would be constructed if they were used.
-- The @Namespace_Root_Node would be passed as the last parameter
-- to dbo.sp_xml_preparedocument
/*************************
-- Create an Internal representation of the XML document.
-- from the @source_data_xml
/*************************
EXECUTE dbo.sp_xml_preparedocument
/*************************
-- If an error was encountered while executing the stored
-- procedure go to the on error section
/*************************
IF @@ERROR <> 0
BEGIN
SELECT @sql_error = -1
,@error_message = @stored_procedure_name + ': ' + 'error executing sp_xml_preparedocument against the xml document'
,@return_code = 60100
GOTO on_error
END
/*************************
-- shred the xml document
/*************************
-- First, insert new records
INSERT INTO Customers
SELECT CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax
FROM OPENXML(@idoc,'/customerDa
WITH ( CustomerID NCHAR(5) '@id'
,CompanyName NVARCHAR(40) 'companyInfo/nameInfo/comp
,ContactName NVARCHAR(50) 'companyInfo/nameInfo/cont
,ContactTitle NVARCHAR(50) 'companyInfo/nameInfo/cont
,Address NVARCHAR(60) 'companyInfo/companyDetail
,City NVARCHAR(15) 'companyInfo/companyDetail
,Region NVARCHAR(15) 'companyInfo/companyDetail
,PostalCode NVARCHAR(10) 'companyInfo/companyDetail
,Country NVARCHAR(15) 'companyInfo/companyDetail
,Phone NVARCHAR(24) 'companyInfo/companyDetail
,Fax NVARCHAR(24) 'companyInfo/companyDetail
)
WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers)
-- Next, Update existing records
UPDATE Customers
SET Customers.CompanyName = myXml.CompanyName
,Customers.ContactName = myXml.ContactName
,Customers.ContactTitle = myXml.ContactTitle
,Customers.Address = myXml.Address
,Customers.City = myXml.City
,Customers.Region = myXml.Region
,Customers.PostalCode = myXml.PostalCode
,Customers.Country = myXml.Country
,Customers.Phone = myXml.Phone
,Customers.Fax = myXml.Fax
FROM OPENXML(@idoc,'/customerDa
WITH ( CustomerID NCHAR(5) '@id'
,CompanyName NVARCHAR(40) 'companyInfo/nameInfo/comp
,ContactName NVARCHAR(50) 'companyInfo/nameInfo/cont
,ContactTitle NVARCHAR(50) 'companyInfo/nameInfo/cont
,Address NVARCHAR(60) 'companyInfo/companyDetail
,City NVARCHAR(15) 'companyInfo/companyDetail
,Region NVARCHAR(15) 'companyInfo/companyDetail
,PostalCode NVARCHAR(10) 'companyInfo/companyDetail
,Country NVARCHAR(15) 'companyInfo/companyDetail
,Phone NVARCHAR(24) 'companyInfo/companyDetail
,Fax NVARCHAR(24) 'companyInfo/companyDetail
)
myXml
WHERE Customers.CustomerID = myXml.CustomerID
/*************************
-- Remove the document
/*************************
EXECUTE dbo.sp_xml_removedocument @iDoc
/*************************
-- If an error was encountered while executing the stored
-- procedure, rollback transaction, go to the on error section
/*************************
IF @@ERROR <> 0
BEGIN
SELECT @sql_error = -1
,@error_message = @stored_procedure_name + ': ' + 'error executing sp_xml_removedocument against the xml document'
,@return_code = 60300
GOTO on_error
END
/*************************
-- on error section
-- log event and return @return code value
/*************************
on_error:
IF @sql_error <> 0
BEGIN
SELECT @error_message = 'Procedure ' + @stored_procedure_name + ' - ' + @error_message + ' ReturnCode = ' + CONVERT(VARCHAR(10), @return_code )
EXECUTE master.dbo.xp_logevent @return_code, @error_message, ERROR
RETURN(@return_code)
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*************************
-- Grant execute permissions on the form
-- stored procedure to the application role
/*************************
GRANT EXECUTE ON dbo.Insert_Update_Customer
GO
Regards,
Mike Sharp
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
PRINT 'Creating stored procedure dbo.Insert_Update_Customer
GO
IF OBJECT_ID('dbo.Insert_Upda
DROP PROCEDURE dbo.Insert_Update_Customer
GO
CREATE PROCEDURE dbo.Insert_Update_Customer
@namespaceUri NVARCHAR(255)
,@source_data_xml NTEXT
)
AS
BEGIN
/*************************
-- PROCEDURE: dbo.Insert_Update_Customer
-- PURPOSE: Inserts or updates customer records based on source.xml
-- Information is passed in three parameters
-- the first parameter, @namespaceUri, contains an optional namespaceURI
-- (exclusive of the prefix used). This is needed to construct the
-- call to sp_xml_preparedocument so that QNames can be used in the
-- XPath expressions for OPENXML(). Currently not used.
-- @source_data_xml contains the data for the document itself.
-- no output results are needed, but success is returned.
-- INPUT:
-- ,@namespaceUri NVARCHAR(255) -- namespaceURI of the xml document
-- ,@source_data_xml NTEXT -- XML Document for operation
-- OUTPUT: None
-- CREATED:
-- DATE AUTHOR
-- 03/02/2005 Mike Sharp
-- UPDATED:
-- DATE AUTHOR
/*************************
SET NOCOUNT ON
/*************************
-- Declare Local Variables
/*************************
DECLARE @idoc INT
,@sql_error INT
,@return_code INT
,@error_message VARCHAR(1000)
,@stored_procedure_name SYSNAME
,@tran_count INT
,@Namespace_Root_Node NVARCHAR(300)
,@Last_Modified_Date DATETIME
,@Last_Modified_User NVARCHAR(128)
,@CustomerID NCHAR(5)
,@CompanyName NVARCHAR(40)
,@ContactName NVARCHAR(50)
,@ContactTitle NVARCHAR(50)
,@Address NVARCHAR(60)
,@City NVARCHAR(15)
,@Region NVARCHAR(15)
,@PostalCode NVARCHAR(10)
,@Country NVARCHAR(15)
,@Phone NVARCHAR(24)
,@Fax NVARCHAR(24)
/*************************
-- seed local variables
/*************************
SELECT @sql_error = 0
,@return_code = 0
,@stored_procedure_name = OBJECT_NAME(@@PROCID)
,@tran_count = @@TRANCOUNT
,@Namespace_Root_Node = '<customerData xmlns:my="' + @namespaceUri + '"/>'
-- Note that this implementation does not need namespaces.
-- This is how it would be constructed if they were used.
-- The @Namespace_Root_Node would be passed as the last parameter
-- to dbo.sp_xml_preparedocument
/*************************
-- Create an Internal representation of the XML document.
-- from the @source_data_xml
/*************************
EXECUTE dbo.sp_xml_preparedocument
/*************************
-- If an error was encountered while executing the stored
-- procedure go to the on error section
/*************************
IF @@ERROR <> 0
BEGIN
SELECT @sql_error = -1
,@error_message = @stored_procedure_name + ': ' + 'error executing sp_xml_preparedocument against the xml document'
,@return_code = 60100
GOTO on_error
END
/*************************
-- shred the xml document
/*************************
-- First, insert new records
INSERT INTO Customers
SELECT CustomerID
,CompanyName
,ContactName
,ContactTitle
,Address
,City
,Region
,PostalCode
,Country
,Phone
,Fax
FROM OPENXML(@idoc,'/customerDa
WITH ( CustomerID NCHAR(5) '@id'
,CompanyName NVARCHAR(40) 'companyInfo/nameInfo/comp
,ContactName NVARCHAR(50) 'companyInfo/nameInfo/cont
,ContactTitle NVARCHAR(50) 'companyInfo/nameInfo/cont
,Address NVARCHAR(60) 'companyInfo/companyDetail
,City NVARCHAR(15) 'companyInfo/companyDetail
,Region NVARCHAR(15) 'companyInfo/companyDetail
,PostalCode NVARCHAR(10) 'companyInfo/companyDetail
,Country NVARCHAR(15) 'companyInfo/companyDetail
,Phone NVARCHAR(24) 'companyInfo/companyDetail
,Fax NVARCHAR(24) 'companyInfo/companyDetail
)
WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers)
-- Next, Update existing records
UPDATE Customers
SET Customers.CompanyName = myXml.CompanyName
,Customers.ContactName = myXml.ContactName
,Customers.ContactTitle = myXml.ContactTitle
,Customers.Address = myXml.Address
,Customers.City = myXml.City
,Customers.Region = myXml.Region
,Customers.PostalCode = myXml.PostalCode
,Customers.Country = myXml.Country
,Customers.Phone = myXml.Phone
,Customers.Fax = myXml.Fax
FROM OPENXML(@idoc,'/customerDa
WITH ( CustomerID NCHAR(5) '@id'
,CompanyName NVARCHAR(40) 'companyInfo/nameInfo/comp
,ContactName NVARCHAR(50) 'companyInfo/nameInfo/cont
,ContactTitle NVARCHAR(50) 'companyInfo/nameInfo/cont
,Address NVARCHAR(60) 'companyInfo/companyDetail
,City NVARCHAR(15) 'companyInfo/companyDetail
,Region NVARCHAR(15) 'companyInfo/companyDetail
,PostalCode NVARCHAR(10) 'companyInfo/companyDetail
,Country NVARCHAR(15) 'companyInfo/companyDetail
,Phone NVARCHAR(24) 'companyInfo/companyDetail
,Fax NVARCHAR(24) 'companyInfo/companyDetail
)
myXml
WHERE Customers.CustomerID = myXml.CustomerID
/*************************
-- Remove the document
/*************************
EXECUTE dbo.sp_xml_removedocument @iDoc
/*************************
-- If an error was encountered while executing the stored
-- procedure, rollback transaction, go to the on error section
/*************************
IF @@ERROR <> 0
BEGIN
SELECT @sql_error = -1
,@error_message = @stored_procedure_name + ': ' + 'error executing sp_xml_removedocument against the xml document'
,@return_code = 60300
GOTO on_error
END
/*************************
-- on error section
-- log event and return @return code value
/*************************
on_error:
IF @sql_error <> 0
BEGIN
SELECT @error_message = 'Procedure ' + @stored_procedure_name + ' - ' + @error_message + ' ReturnCode = ' + CONVERT(VARCHAR(10), @return_code )
EXECUTE master.dbo.xp_logevent @return_code, @error_message, ERROR
RETURN(@return_code)
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*************************
-- Grant execute permissions on the form
-- stored procedure to the application role
/*************************
GRANT EXECUTE ON dbo.Insert_Update_Customer
GO
I just noticed a bunch of junk that's in there but not needed (I pasted this from an application I'm working on) This stuff:
DECLARE
[... snip ...]
,@Last_Modified_Date DATETIME
,@Last_Modified_User NVARCHAR(128)
,@CustomerID NCHAR(5)
,@CompanyName NVARCHAR(40)
,@ContactName NVARCHAR(50)
,@ContactTitle NVARCHAR(50)
,@Address NVARCHAR(60)
,@City NVARCHAR(15)
,@Region NVARCHAR(15)
,@PostalCode NVARCHAR(10)
,@Country NVARCHAR(15)
,@Phone NVARCHAR(24)
,@Fax NVARCHAR(24)
isn't needed.
DECLARE
[... snip ...]
,@Last_Modified_Date DATETIME
,@Last_Modified_User NVARCHAR(128)
,@CustomerID NCHAR(5)
,@CompanyName NVARCHAR(40)
,@ContactName NVARCHAR(50)
,@ContactTitle NVARCHAR(50)
,@Address NVARCHAR(60)
,@City NVARCHAR(15)
,@Region NVARCHAR(15)
,@PostalCode NVARCHAR(10)
,@Country NVARCHAR(15)
,@Phone NVARCHAR(24)
,@Fax NVARCHAR(24)
isn't needed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Mike,
Do I need to download and install SQL XML 3.0 for ASP.net as well?
Can you the same console application code for ASP.net? Do you have that version for me to
try this?
Deepa
Do I need to download and install SQL XML 3.0 for ASP.net as well?
Can you the same console application code for ASP.net? Do you have that version for me to
try this?
Deepa
There's only one SQL XML 3.0. It can be used on any machine, whether or not it has SQL Server. However I would also install it on the SQL Server machine, as it will update the parser that SQL Server uses for OpenXML.
I don't have any other code, I just wrote what I posted. The code is basically the same whether you're doing it as a console app, a windows app or a web app. You'll have to do that in your web application.
Regards,
Mike Sharp
I don't have any other code, I just wrote what I posted. The code is basically the same whether you're doing it as a console app, a windows app or a web app. You'll have to do that in your web application.
Regards,
Mike Sharp
ASKER
Mike,
I have this code in ASP.net. Appreciate your input.
--------------------------
Imports System.Text
Imports System.Xml
Imports Microsoft.Data.SQLXML
Imports System.Data.SqlClient
Imports System
Public Class xmltest
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim xml As XmlDocument = New XmlDocument
Dim bUpdate As Boolean = True
Dim pause As String
Response.Write("true")
' Response.End()
xml.PreserveWhitespace = True
xml.Load(Server.MapPath("P
Response.Write(xml.OuterXm
UpdateDb(xml.DocumentEleme
Console.WriteLine("databas
pause = Console.ReadLine()
End Sub
Public Sub UpdateDb(ByVal xml As String, ByVal xmlns As String)
Dim oConn As New SqlConnection("Server=DEPL
oConn.Open()
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "Insert_Update_Customers_s
'SqlCommandBuilder.DeriveP
'cmd.Parameters.Item("@nam
' cmd.Parameters.Item("@sour
'cmd.ExecuteReader()
cmd.ExecuteReader()
cmd.Dispose()
cmd = Nothing
oConn.Close()
oConn = Nothing
End Sub
End Class
--------------------------
I am getting this following error. Any idea why?
Server Error in '/cbs' Application.
--------------------------
ExecuteReader: Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationExc
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
cbs.xmltest.UpdateDb(Strin
cbs.xmltest.Page_Load(Obje
System.Web.UI.Control.OnLo
System.Web.UI.Control.Load
System.Web.UI.Page.Process
I have this code in ASP.net. Appreciate your input.
--------------------------
Imports System.Text
Imports System.Xml
Imports Microsoft.Data.SQLXML
Imports System.Data.SqlClient
Imports System
Public Class xmltest
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim xml As XmlDocument = New XmlDocument
Dim bUpdate As Boolean = True
Dim pause As String
Response.Write("true")
' Response.End()
xml.PreserveWhitespace = True
xml.Load(Server.MapPath("P
Response.Write(xml.OuterXm
UpdateDb(xml.DocumentEleme
Console.WriteLine("databas
pause = Console.ReadLine()
End Sub
Public Sub UpdateDb(ByVal xml As String, ByVal xmlns As String)
Dim oConn As New SqlConnection("Server=DEPL
oConn.Open()
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedur
cmd.CommandText = "Insert_Update_Customers_s
'SqlCommandBuilder.DeriveP
'cmd.Parameters.Item("@nam
' cmd.Parameters.Item("@sour
'cmd.ExecuteReader()
cmd.ExecuteReader()
cmd.Dispose()
cmd = Nothing
oConn.Close()
oConn = Nothing
End Sub
End Class
--------------------------
I am getting this following error. Any idea why?
Server Error in '/cbs' Application.
--------------------------
ExecuteReader: Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationExc
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
System.Data.SqlClient.SqlC
cbs.xmltest.UpdateDb(Strin
cbs.xmltest.Page_Load(Obje
System.Web.UI.Control.OnLo
System.Web.UI.Control.Load
System.Web.UI.Page.Process
ASKER
Mike,
I am trying to execute this directly in SQL Query Analyzer, it is not creating new records from XML.
Insert_Update_Customers_sp
<customerData xmlns="http://tempuri.org/customers.xsd">
<customer id="WID10">
<companyInfo>
<nameInfo>
<companyName>Widgets Inc.</companyName>
<contactName name="John Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Anywhere" city="Pinetop" state="AZ" zip="85244" country="USA" />
<phone>
<busPhone busLine="520 123-1234" mobile="520 123-1238" />
<busFax busLine="520 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
<customer id="CarpA">
<companyInfo>
<nameInfo>
<companyName>Carpet Inc.</companyName>
<contactName name="Jane Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Red Carpet St." city="Phoenix" state="AZ" zip="85999" country="USA" />
<phone>
<busPhone busLine="480 123-1234" mobile="480 123-1238" />
<busFax busLine="480 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
</customerData>'
I am trying to execute this directly in SQL Query Analyzer, it is not creating new records from XML.
Insert_Update_Customers_sp
<customerData xmlns="http://tempuri.org/customers.xsd">
<customer id="WID10">
<companyInfo>
<nameInfo>
<companyName>Widgets Inc.</companyName>
<contactName name="John Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Anywhere" city="Pinetop" state="AZ" zip="85244" country="USA" />
<phone>
<busPhone busLine="520 123-1234" mobile="520 123-1238" />
<busFax busLine="520 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
<customer id="CarpA">
<companyInfo>
<nameInfo>
<companyName>Carpet Inc.</companyName>
<contactName name="Jane Doe" title="President" />
</nameInfo>
<companyDetails>
<address street="1234 Red Carpet St." city="Phoenix" state="AZ" zip="85999" country="USA" />
<phone>
<busPhone busLine="480 123-1234" mobile="480 123-1238" />
<busFax busLine="480 123-1235" />
</phone>
</companyDetails>
</companyInfo>
</customer>
</customerData>'
ASKER
Mike - Ignore my comment. The stored procedure works fine. Thanks so much.
Well, the XML you posted earlier did not have the namespace...that's why it's not adding the records. You need to modify the sproc with:
EXECUTE dbo.sp_xml_preparedocument
and then you have to qualify every namespaced node in each of the XPath expressions in the open XML.
In XML it's important to realize that namespaces are significant.
Regards,
Mike Sharp
EXECUTE dbo.sp_xml_preparedocument
and then you have to qualify every namespaced node in each of the XPath expressions in the open XML.
In XML it's important to realize that namespaces are significant.
Regards,
Mike Sharp
1) The easiest way to accomplish this task, especially if you don't have much experience with directly manipulating XML, is to load the XML into a System.Data.DataSet object and operate on the data as if it were a collection of tables. Sounds like there may be only a single table in your data, making it even easier.
dim ds as new DataSet
ds.ReadXML( ... ) '<< not sure where your data comes from
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassreadxmltopic2.asp
Once inside the DataSet you can iterate through the rows in the table(s) and perform whatever actions are needed to differentiate between existing versus new records. You could even go so far as setting the RowState flag for each row, then running the whole DataSet through a DataAdapter.Update process, which will perform the INSERT and UPDATE actions automatically (assuming you've configured the underlying statements correctly).
dim row As DataRow
for each row In ds.tables(0).rows '<< dataset ds from above
'... examine columns, etc
next row
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatatableclassrowstopic.asp
In short, depending on your comfort level with XML, you could bypass XML entirely and convert this into a classic procedural programming problem using the incredibly powerful DataSet classes.
2) If you don't know much about DataSets, then another approach is to open the XML document with a System.Xml.XmlTextReader and react to the various element nodes as you encounter them, doing SQL insert/update as needed.
dim reader as new XmlTextReader( ... ) '<< Filename or source stream
while reader.Read()
if reader.NodeType = XmlNodeType.Element
'... check element name, react to key values
end if
end while
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemxmlxmltextreaderclassreadtopic.asp
3) If you will need to make multiple passes through the XML data and don't mind the overhead of loading the entire thing into memory you could load the XML into a System.Xml.XmlDocument object and work through the nodes, similar to option #2.
dim doc as XmlDocument = new XmlDocument()
doc.Load( ... ) '<< file or source stream
dim node as XmlNode
dim nodes as XmlNodeList
doc.SelectNodes( ... ) '<< XPath node selection expression
for each node in nodes
'... react to the nodes you find
next
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemxmlxmlnodeclassselectnodestopic.asp
4) You could write an XSLT stylesheet that generates INSERT and UPDATE statements, but this would be more trouble than it's worth, especially if you have to make database calls to determine if a record is new or existing.
5) SQL Server has support for opening and reacting to the contents of XML documents, but these features are fairly crude in this version of SQL Server and would involve some complicated programming on the server side.
I'd go with option #1 if you already know something about working with DataSets and are short on XML knowledge. I'd go with option #2 if you'd rather work with XML concepts more directly.
Pick an option and provide more details (especially some sample XML and an idea about the SQL Server table structure) and myself or someone else can provide more specific recommendations.
-- Craig Yellick