XML as parameter to stored procedure
J
of a great advantage to using this technique. Instead of having to manage
transactions spaning over multiple stored procedures, XML can be used to
passing the entire transaction in one stored procedure.
Grant Case
We use this thing all the time in our business. You will find a number of
individuals on the board that advocate using a string manipulation method.
This method can be faster, but for extendability I do not think you can beat
using OPENXML in SQL Server. Another good thing about using the XML method
is passing in many rows from a page. For instance in some of our
applications we have twenty or thirty rows of information the user can
process. Instead of having to manage the transaction at the client, the
client sends all the information as an XML string. The stored procedure then
opens up the XML string, parses it, and dumps it into a temporary or
variable table which then can be manipulated using standard methods. I am
attaching a portion of a stored procedure to help you on your way. Good
luck!
Grant
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[uspApprTrckInsUpdtApprsr]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[uspApprTrckInsUpdtApprsr]
GO
CREATE PROCEDURE dbo.uspApprTrckInsUpdtApprsr
@StrXML AS TEXT,
@ErrorCode AS INT OUTPUT
AS
/*********************************HDR*************************************
*********GENERAL*********
OBJECT NAME: uspApprTrckInsUpdtApprsr
OBJECT TYPE: Stored Procedure
SUBCATEGORY: Application - Appraisal Tracker
CREATED BY: GRANT CASE
DATE: 11/26/2004
MODIFIED BY: GRANT CASE
DATE: 11/26/2004
DESCRIPTION:
<Root>
<AppraisalTracker>
<Appraiser>
<AppraiserNumber>28</AppraiserNumber>
<Action>Insert</Action>
<AppraiserName>Lisa Victory</AppraiserName>
<Company>RBCBF</Company>
<Address1>11011 Richmond</Address1>
<Address2>Suite 850</Address2>
<City>Houston</City>
<State>TN</State>
<Zip>77545</Zip>
<Phone>(111) 111-1111</Phone>
<Fax>(222) 222-2222</Fax>
<Pager>(281) 454-5548</Pager>
<CellNum>(333) 333-3333</CellNum>
<Email>lisa.v...@rbc.com</Email>
<CertificationNumber>D343</CertificationNumber>
<LicenseExpiration>11/27/2004</LicenseExpiration>
<VendorType>A</VendorType>
<Active>1</Active>
<Comments>Testing...</Comments>
</Appraiser>
</AppraisalTracker>
</Root>
*********VARIABLES*********
NAME TYPE LOCAL/PASSED DEFAULT
DESCRIPTION
---------------------------------------------------------------
@StrXML NTEXT PASSED
The XML string containing values to populate in the Documentation table.
@IDOC INT LOCAL
Index number of the internal SQL representation of the XML document.
@ErrorCode INT PASSED/OUTPUT
ErrorCode denoting success or failure of the SP
@ErrorCounter INT LOCAL
Counts the number of errors that occur in the stored procedure.
*********DEPENDENCIES*********
TABLES:
OBJECTS:
*********TEMP TABLES*********
NAME DESCRIPTION/USE
*********TRIGGER SPECIFIC*********
SOURCE TABLE:
DESTINATION TABLE:
CASCADED TRG:
CASCADED TRG TABLE:
*********REPORT SPECIFIC*********
REPORTS USING STORED PROCEDURE:
*********OUTPUT*********
*********MODIFICATION LOG*********
DATE INITIALS MODIFICATION
11/26/2004 GSC Created
*********HEADER CONVENTIONS*********
DO NOT GO PAST 75 CHARACTERS BEFORE GOING TO THE NEXT LINE
TO DEBUG, REPLACE ALL "-- DEBUG"
*********************************HDR*************************************/
--------------------------------------------------------------------------
-- LOCAL VARIABLE DECLARATION SECTION
--------------------------------------------------------------------------
DECLARE @ErrorCounter AS INT
DECLARE @IDOC INT
--------------------------------------------------------------------------
-- LOCAL VARIABLE SET SECTION
--------------------------------------------------------------------------
--IF YOU DO NOT SET THIS, ACTIVE SERVER PAGES WILL HAVE PROBLEMS
SET NOCOUNT ON
--Setting will Rollback Transactions Orphaned to the Connection
IF @@TRANCOUNT <> 0 ROLLBACK
SET @ErrorCode = 0
SET @ErrorCounter = 0
EXEC sp_xml_preparedocument @IDOC OUTPUT, @StrXML
IF @@ERROR <> 0
BEGIN
SET @ErrorCounter = @ErrorCounter + 1
SET @ErrorCode = 1
RETURN
END
--------------------------------------------------------------------------
-- TEMP TABLE CREATION SECTION
--------------------------------------------------------------------------
CREATE TABLE #Appraiser
(
AppraiserNumber VARCHAR (4),
Action VARCHAR (25),
AppraiserName VARCHAR (50),
Company VARCHAR (50),
Address1 VARCHAR (50),
Address2 VARCHAR (50),
City VARCHAR (45),
State VARCHAR (2),
Zip VARCHAR (15),
Phone VARCHAR (15),
Fax VARCHAR (15),
Pager VARCHAR (15),
CellNum VARCHAR (15),
Email VARCHAR (50),
CertificationNumber VARCHAR (50),
LicenseExpiration SMALLDATETIME,
VendorType VARCHAR (1),
Active INT,
Comments TEXT
)
--------------------------------------------------------------------------
-- CODE SECTION
--------------------------------------------------------------------------
INSERT INTO #Appraiser (AppraiserNumber, [Action], AppraiserName, Company,
Address1, Address2, City, State, Zip, Phone, Fax, Pager, CellNum, Email,
CertificationNumber, LicenseExpiration, VendorType , Active, Comments)
SELECT AppraiserNumber, [Action], AppraiserName, Company,
Address1, Address2, City, State, Zip, Phone, Fax, Pager, CellNum, Email,
CertificationNumber, LicenseExpiration, VendorType , Active, Comments
FROM OPENXML (@IDOC, 'Root/AppraisalTracker/Appraiser',2)
WITH (
AppraiserNumber VARCHAR (4),
Action VARCHAR (25),
AppraiserName VARCHAR (50),
Company VARCHAR (50),
Address1 VARCHAR (50),
Address2 VARCHAR (50),
City VARCHAR (45),
State VARCHAR (2),
Zip VARCHAR (15),
Phone VARCHAR (15),
Fax VARCHAR (15),
Pager VARCHAR (15),
CellNum VARCHAR (15),
Email VARCHAR (50),
CertificationNumber VARCHAR (50),
LicenseExpiration SMALLDATETIME,
VendorType VARCHAR (1),
Active INT,
Comments TEXT
)
IF @@ERROR <> 0
BEGIN
SET @ErrorCounter = @ErrorCounter + 1
SET @ErrorCode = 2
EXEC sp_xml_removedocument @IDOC
RETURN
END
EXEC sp_xml_removedocument @IDOC
"J" <j...@h.com> wrote in message news:bAVMd.44$zs.11@okepread04...