?
Solved

Import XML data into SQL Server (Asp.NET)

Posted on 2005-03-01
20
Medium Priority
?
2,250 Views
Last Modified: 2013-11-19
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
0
Comment
Question by:dkr0001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
20 Comments
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13435594
Like everything with XML, there are many options.

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
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 13436011
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
0
 

Author Comment

by:dkr0001
ID: 13439638
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dkr0001
ID: 13440195
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("productxml/customers.xml"))

        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

0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13440390
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
0
 

Author Comment

by:dkr0001
ID: 13441140
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
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13441498
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
0
 

Author Comment

by:dkr0001
ID: 13442356

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
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 13443345
"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
0
 
LVL 11

Expert Comment

by:CraigYellick
ID: 13443535
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.
0
 

Author Comment

by:dkr0001
ID: 13444567
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
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 13445622
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_Customers_sp...'

GO

IF OBJECT_ID('dbo.Insert_Update_Customers_sp') IS NOT NULL
   DROP PROCEDURE dbo.Insert_Update_Customers_sp

GO

CREATE PROCEDURE dbo.Insert_Update_Customers_sp(
     @namespaceUri       NVARCHAR(255)
    ,@source_data_xml     NTEXT
)

AS

BEGIN

/*****************************************************************/
-- PROCEDURE: dbo.Insert_Update_Customers_sp

-- 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 @idoc  OUTPUT, @source_data_xml

/**********************************************************/
-- 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,'/customerData/customer',1)
  WITH ( CustomerID                 NCHAR(5)        '@id'
        ,CompanyName                NVARCHAR(40)    'companyInfo/nameInfo/companyName'
        ,ContactName                NVARCHAR(50)    'companyInfo/nameInfo/contactName'
        ,ContactTitle               NVARCHAR(50)    'companyInfo/nameInfo/contactName/@title'
        ,Address                    NVARCHAR(60)    'companyInfo/companyDetails/address/@street'
        ,City                       NVARCHAR(15)    'companyInfo/companyDetails/address/@city'
        ,Region                     NVARCHAR(15)    'companyInfo/companyDetails/address/@state'
        ,PostalCode                 NVARCHAR(10)    'companyInfo/companyDetails/address/@zip'
        ,Country                    NVARCHAR(15)    'companyInfo/companyDetails/address/@country'
        ,Phone                      NVARCHAR(24)    'companyInfo/companyDetails/busPhone/@busLine'
        ,Fax                        NVARCHAR(24)    'companyInfo/companyDetails/busFax/@busLine'
       )
       
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,'/customerData/customer',1)
  WITH ( CustomerID                 NCHAR(5)        '@id'
        ,CompanyName                NVARCHAR(40)    'companyInfo/nameInfo/companyName'
        ,ContactName                NVARCHAR(50)    'companyInfo/nameInfo/contactName'
        ,ContactTitle               NVARCHAR(50)    'companyInfo/nameInfo/contactName/@title'
        ,Address                    NVARCHAR(60)    'companyInfo/companyDetails/address/@street'
        ,City                       NVARCHAR(15)    'companyInfo/companyDetails/address/@city'
        ,Region                     NVARCHAR(15)    'companyInfo/companyDetails/address/@state'
        ,PostalCode                 NVARCHAR(10)    'companyInfo/companyDetails/address/@zip'
        ,Country                    NVARCHAR(15)    'companyInfo/companyDetails/address/@country'
        ,Phone                      NVARCHAR(24)    'companyInfo/companyDetails/busPhone/@busLine'
        ,Fax                        NVARCHAR(24)    'companyInfo/companyDetails/busFax/@busLine'
       )
       
       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_Customers_sp TO public

GO

0
 
LVL 26

Expert Comment

by:rdcpro
ID: 13445634
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.
0
 
LVL 26

Accepted Solution

by:
rdcpro earned 400 total points
ID: 13446516
Ok, I just ran a test, and there were a couple XPath bugs in my sproc, but this updated version works well.  When I post your XML, it inserts both customers into the customers table, and when I change "John Doe" to "John Smallberries" and run it again, it updates that row in the table.  Code to follow below.

Regards,
Mike Sharp


Stored Procedure (make sure you change the last line so that only the database role you want is allowed to execute!):


GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO


PRINT 'Creating stored procedure dbo.Insert_Update_Customers_sp...'

GO

IF OBJECT_ID('dbo.Insert_Update_Customers_sp') IS NOT NULL
   DROP PROCEDURE dbo.Insert_Update_Customers_sp

GO

CREATE PROCEDURE dbo.Insert_Update_Customers_sp(
     @namespaceUri       NVARCHAR(255)
    ,@source_data_xml     NTEXT
)

AS

BEGIN

/*****************************************************************/
-- PROCEDURE: dbo.Insert_Update_Customers_sp

-- 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)


/**********************************************************/
-- 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 @idoc  OUTPUT, @source_data_xml

/**********************************************************/
-- 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

/**********************************************************/
-- extract form values from xml document
/**********************************************************/


-- First, insert new records

INSERT INTO Customers

SELECT   CustomerID  
        ,CompanyName
        ,ContactName
        ,ContactTitle
        ,Address    
        ,City        
        ,Region      
        ,PostalCode  
        ,Country    
        ,Phone      
        ,Fax        

  FROM OPENXML(@idoc,'/customerData/customer',1)
  WITH ( CustomerID                 NCHAR(5)        '@id'
        ,CompanyName                NVARCHAR(40)    'companyInfo/nameInfo/companyName'
        ,ContactName                NVARCHAR(50)    'companyInfo/nameInfo/contactName/@name'
        ,ContactTitle               NVARCHAR(50)    'companyInfo/nameInfo/contactName/@title'
        ,Address                    NVARCHAR(60)    'companyInfo/companyDetails/address/@street'
        ,City                       NVARCHAR(15)    'companyInfo/companyDetails/address/@city'
        ,Region                     NVARCHAR(15)    'companyInfo/companyDetails/address/@state'
        ,PostalCode                 NVARCHAR(10)    'companyInfo/companyDetails/address/@zip'
        ,Country                    NVARCHAR(15)    'companyInfo/companyDetails/address/@country'
        ,Phone                      NVARCHAR(24)    'companyInfo/companyDetails/phone/busPhone/@busLine'
        ,Fax                        NVARCHAR(24)    'companyInfo/companyDetails/phone/busFax/@busLine'
       )
       
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,'/customerData/customer',1)
  WITH ( CustomerID                 NCHAR(5)        '@id'
        ,CompanyName                NVARCHAR(40)    'companyInfo/nameInfo/companyName'
        ,ContactName                NVARCHAR(50)    'companyInfo/nameInfo/contactName/@name'
        ,ContactTitle               NVARCHAR(50)    'companyInfo/nameInfo/contactName/@title'
        ,Address                    NVARCHAR(60)    'companyInfo/companyDetails/address/@street'
        ,City                       NVARCHAR(15)    'companyInfo/companyDetails/address/@city'
        ,Region                     NVARCHAR(15)    'companyInfo/companyDetails/address/@state'
        ,PostalCode                 NVARCHAR(10)    'companyInfo/companyDetails/address/@zip'
        ,Country                    NVARCHAR(15)    'companyInfo/companyDetails/address/@country'
        ,Phone                      NVARCHAR(24)    'companyInfo/companyDetails/phone/busPhone/@busLine'
        ,Fax                        NVARCHAR(24)    'companyInfo/companyDetails/phone/busFax/@busLine'
       )
       
       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_Customers_sp TO public

GO


==================================================================



Console Application in C# to do the work.  Download and install SQL XML 3.0, and add it as a reference in the project.  Then use this code to test/run it:

using System;
using System.Text;
using System.Xml;
using Microsoft.Data.SqlXml;

namespace basicDOM
{
      /// <summary>
      /// Summary description for Class1.
      /// </summary>
      class Class1
      {
            static XmlDocument xmlDoc = new XmlDocument();

            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main(string[] args)
            {
                  XmlDocument xml = new XmlDocument();
                  xml.PreserveWhitespace = true;
                  xml.Load("customerdata.xml");
                  Console.WriteLine(xml.OuterXml);
                  // Converting our XML into a string forces it to be UTF-16, so we want to
                  // remove the processing instruction that used to say UTF-8
                  // Of course, it would be best to explicitly handle the encoding.
                  bool bUpdate = UpdateDb(xml.DocumentElement.OuterXml, "");
                  Console.WriteLine("database updated: " + bUpdate.ToString());
                  string pause = Console.ReadLine();
            }

            /// <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>
            /// <param name="xmlns">The namespaceURI, if any</param>
            /// <returns>boolean</returns>
            static bool UpdateDb(string xml, string xmlns)
            {
                  SqlXmlParameter SourceNamespace;
                  SqlXmlParameter SourceDataXml;
                  try
                  {
                        SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLOLEDB;Server=DevVsMichaels03;database=Northwind;Trusted_Connection=yes;");
                        cmd.CommandType = SqlXmlCommandType.Sql;
                        cmd.CommandText = "Execute Insert_Update_Customers_sp ?, ?";
                        SourceNamespace = cmd.CreateParameter();
                        SourceNamespace.Name = "namespaceUri";
                        SourceNamespace.Value = xmlns;
                        SourceDataXml = cmd.CreateParameter();
                        SourceDataXml.Name = "source_data_xml";
                        SourceDataXml.Value = xml;
                        try
                        {
                              cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                              Console.WriteLine("Exception during ExecuteNonQuery...Update failed: " + ex.Message + "[" + ex.Source + "]" + ex.StackTrace);
                              return false;
                        }
                  }
                  catch(Exception ex)
                  {
                        Console.WriteLine("Could not construct SqlXmlCommand: " + ex.Message + "[" + ex.Source + "]" + ex.StackTrace);
                        return false;
                  }
                  return true;


            }

      }
}



 
0
 

Author Comment

by:dkr0001
ID: 13449505
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
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 13449996
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
0
 

Author Comment

by:dkr0001
ID: 13451174
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("ProductXML/customers.xml"))
        Response.Write(xml.OuterXml)

        UpdateDb(xml.DocumentElement.OuterXml, "")
        Console.WriteLine("database updated: " + bUpdate.ToString())
        pause = Console.ReadLine()

    End Sub

    Public Sub UpdateDb(ByVal xml As String, ByVal xmlns As String)

        Dim oConn As New SqlConnection("Server=DEPLOY;UID=sa;PWD=;Database=Northwind;")
        oConn.Open()
        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure

        cmd.CommandText = "Insert_Update_Customers_sp, 'http://tempuri.org/customers.xsd','" & Server.MapPath("ProductXML/customers.xml") & "', oConn"
        'SqlCommandBuilder.DeriveParameters(cmd)

        'cmd.Parameters.Item("@namespaceUri").Value = "http://tempuri.org/customers.xsd"
        ' cmd.Parameters.Item("@source_data_xml").Value = Server.MapPath("ProductXML/customers.xml")
        '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.InvalidOperationException: ExecuteReader: Connection property has not been initialized.

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: ExecuteReader: Connection property has not been initialized.]
   System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean executing) +251
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +80
   System.Data.SqlClient.SqlCommand.ExecuteReader() +42
   cbs.xmltest.UpdateDb(String xml, String xmlns) in \\deploy\wwwroot$\cbs\xmltest.aspx.vb:62
   cbs.xmltest.Page_Load(Object sender, EventArgs e) in \\deploy\wwwroot$\cbs\xmltest.aspx.vb:43
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +750

 
0
 

Author Comment

by:dkr0001
ID: 13451431
Mike,

I am trying to execute this directly in SQL Query Analyzer, it is not creating new records from XML.

Insert_Update_Customers_sp 'http://tempuri.org/customers.xsd','<?xml version="1.0"?>
<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>'
0
 

Author Comment

by:dkr0001
ID: 13451540
Mike - Ignore my comment. The stored procedure works fine. Thanks so much.
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 13451566
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 @idoc  OUTPUT, @source_data_xml, @Namespace_Root_Node

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
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question