|
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;
}
}
}
XML file tried to load
--------------------------------
<?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>
-------------------------------
Load with error
------------------
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>'
--------------------------------------------
reasonse for error
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.
Posted by Mike Sharp
|
