XML Argument Protocols for SQL 2005 Stored Procedures

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jack Hummer » XML Argument Protocols for SQL 2005 Stored...

XML Argument Protocols for SQL 2005 Stored Procedures

Rate Topic

Display Mode

Topic Options

Author

Message

jack a hummer

Posted Monday, September 25, 2006 4:11 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:15 AM
Points: 20, Visits: 227

Comments posted here are about the content posted at temp

Post #311074

Regan Galbraith

Posted Friday, October 06, 2006 6:24 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, November 30, 2012 12:17 AM
Points: 86, Visits: 252

comment 1
---quote----
XML As A Solution

T-SQL has native support for manipulating XML in SQL 2000, but with the release of SQL 2005, XML support is even better. T-SQL arrives with additional functions and an actual XML native datatype [essentially an nvarchar(max) with storage in "optimised UTF-16 characters" per BOL]. There are some curious comments in the BOL about the overhead of XML processing, but I have not yet tried to measure it. My general rule is, if a certain syntax or process makes for increases in programming efficiency and reliability as well as reducing long term maintenance, I care less about a little additional overhead on the machine.
---/quote----

An interesting point, but I'm not sure I would agree with the statement: 'I care less about a little additional overhead on the machine.' On a highly transactional system, a 'little overhead in handling XML' across a lot of XML handling SP can add up to a headache.

comment 2

As another piece you may find interesting, here's an example of a stored procedure that receives XML as a variable, and an xpath query as a variable, and then uses the xpath query to understand and use the XML retrieved.:

------------code below---------------- I had to 'escape' the Less Than's to post, so becare if copying and testing. Also, TABS seem to have been removed - it is properly indented Smile

---
--
################################################## CREATE PROCEDURE ################################################################################

CREATE PROCEDURE integration.[GetProductInformationEnhancementsXML]
@CatalogueNumberList XML, --This XML is provided by the calling application, and contains a list of CatalogueItemIDs
@CatalogueNumberSelectXPath NVarChar(255) --This XPATH query is provided by the calling application, as the method to extract the
--information from the XML provided. This allows the calling application to alter the XML
--structure, if necessary, and provide the relevant method for extracting the data the SP
--requires, without any changes to the SP.
AS
BEGIN --Procedure
--################################################## COMMENTS ########################################################################################

--Created By: Regan Galbraith
--Created On: 2006-07-07

--Version: 0.3 - 2006-08-04 - Enhanced to add the BuyListSequence element.

--SQL Version: 2005 SP1. Uses TRY/CATCH, XPath.

--Overview:
-- This script is a SP retrieving the data from the staging tables:
-- which will have been populated by an SSIS package, inserting this data. This then generates a resultset formatted as XML, and returns.
-- The XML maps to the following schema:
-- < ProductInformationEnhancementsDS>
-- < ProductInformationEnhancements CatalogueItemID="1">
-- < BuyList __Ref="BuyListID" BuyListID="15" BuyListName="Strata" BuyListSequence="2"/>
-- < BuyList __Ref="BuyListID" BuyListID="20" BuyListName="Strata2" BuyListSequence="1"/>
-- < PriceCheck CompetitorName="Acme" CompetitorPrice="8.99" CheckDate="2006-06-17"/>
-- < PriceCheck CompetitorName="Acme2" CompetitorPrice="8.99" CheckDate="2006-06-17"/>
-- < BuyListSequence __Value="2" __Name="BuyListSequence_15" />
-- < BuyListSequence __Value="1" __Name="BuyListSequence_20" />
-- < UnitsSold __Value="34"/>
-- < /ProductInformationEnhancements>
-- < ProductInformationEnhancements CatalogueItemID="2">
-- < PriceCheck CompetitorName="Acme" CompetitorPrice="5.00" CheckDate="2006-06-17"/>
-- < /ProductInformationEnhancements>
-- < ProductInformationEnhancements CatalogueItemID="3">
-- < PriceCheck CompetitorName="Acme" CompetitorPrice="67.00" CheckDate="2006-06-17"/>
-- < /ProductInformationEnhancements>
-- < ProductInformationEnhancements CatalogueItemID="4">
-- < PriceCheck CompetitorName="Acme2" CompetitorPrice="78.00" CheckDate="2006-06-17"/>
-- < /ProductInformationEnhancements>
-- < /ProductInformationEnhancementsDS>

--ToDo:

--Sample Usage:
--DECLARE @RC int
--DECLARE
-- @CatalogueNumberList xml,
-- @CatalogueNumberSelectXPath nvarchar(255)
--
--set @CatalogueNumberList = '< ProductInformationEnhancementsRequest DataViewType="Raw" >
--< CatalogueNumbers>
-- < CatalogueNumber>1 < /CatalogueNumber>
-- < CatalogueNumber>2 < /CatalogueNumber>
-- < CatalogueNumber>3 < /CatalogueNumber>
-- < CatalogueNumber>4 < /CatalogueNumber>
--< /CatalogueNumbers>
--< /ProductInformationEnhancementsRequest>'
--set @CatalogueNumberSelectXPath = '/*[local-name()="ProductInformationEnhancementsRequest"]/CatalogueNumbers/CatalogueNumber/text()'
--EXECUTE @RC = [integration].[GetProductInformationEnhancementsXML]
-- @CatalogueNumberList=@CatalogueNumberList,
-- @CatalogueNumberSelectXPath=@CatalogueNumberSelectXPath
--IF @RC <> 0 PRINT 'Error'
--ELSE SELECT 'Success'
--################################################## DECLARE & SET VARIABLES #########################################################################
SET NOCOUNT ON
DECLARE
@ErrorDescription VARCHAR(512),
@Error INT,
@RowCount INT,
@XMLHandle INT,
@ErrorProcedure SYSNAME,
@ErrorLine INT

DECLARE
@CatalogueItemTable TABLE ([CatalogueItemID] NCHAR(8))

--Initialize variables
--################################################## Procedure Code Start ############################################################################
-- Open a TRY block so that any errors with severity > 10 will be caught in a central place (the CATCH block below)
BEGIN TRY

--################################################## Pre-Contract Check ##########################################################################
-- inputs, so contract check
IF (@CatalogueNumberList IS NULL) BEGIN
SET @ErrorDescription = '### ERROR - Pre-CONTRACT FAILURE : Stored Procedure received an invalid input parameter @CatalogueNumberList: ['
+COALESCE(CONVERT(NVARCHAR(MAX),@CatalogueNumberList),'NULL')+'] and is FAILING - ERROR ###'
RAISERROR (@ErrorDescription,16,1)
END

IF COALESCE(@CatalogueNumberSelectXPath,'') = '' BEGIN
SET @ErrorDescription = '### ERROR - Pre-CONTRACT FAILURE : Stored Procedure received an invalid input parameter @CatalogueNumberSelectXPath:'
+' ['+RTRIM(COALESCE(@CatalogueNumberSelectXPath,'NULL'))+'] and is FAILING - ERROR ###'
RAISERROR (@ErrorDescription,16,1)
END
--################################################## Execute Code ################################################################################
--Read the input XML and store the provided CatalogueItemID's in a table variable, to use to adjust the resultset
--Allocate handle to input xml variable document
EXEC @Error = sp_xml_preparedocument @XMLHandle OUTPUT, @CatalogueNumberList
--ErrorChecking
IF @Error <> 0 BEGIN
SET @ErrorDescription = '### ERROR - SQL FAILURE : Stored Procedure failed with @@ERROR of ['+CONVERT(VARCHAR(16),@Error)
+'] trying to run [sp_xml_preparedocument] and is FAILING - ERROR ###'
RAISERROR (@ErrorDescription,16,1)
END
--get list of CatalogueItemID from the XML
INSERT INTO @CatalogueItemTable ([CatalogueItemID])
SELECT CONVERT(NCHAR(8),[text])
FROM OPENXML (@XMLHandle,@CatalogueNumberSelectXPath ,3)
--free XML document
EXEC @Error = sp_xml_removedocument @XMLHandle
--ErrorChecking
IF @Error <> 0 BEGIN
SET @ErrorDescription = '### ERROR - SQL FAILURE : Stored Procedure failed with @@ERROR of ['+CONVERT(VARCHAR(16),@Error)
+'] trying to run [sp_xml_removedocument] and is FAILING - ERROR ###'
RAISERROR (@ErrorDescription,16,1)
END

--Generate the ProductInformationEnhancement XML
SELECT
NULL as "@ProductInformationEnhancementsDS",--Allocates a Named Root
(SELECT
--ProductInformationEnhancements CatalogueItemID
CL.[CatalogueItemID] as "@CatalogueItemID",
--buylist
( SELECT
'BuyListID' as "@__Ref",
BL.[BuyListId] as "@BuyListID",
BL.[BuyListName] as "@BuyListName",
BL.[CatalogueItemBuyListSequenceNumber] as "@BuyListSequence"
FROM
[integration].[BuyListProductRecipes] BL
WHERE
BL.[CatalogueItemID] = CL.[CatalogueItemID]
AND CURRENT_TIMESTAMP BETWEEN BL.BuyListStartDate AND BL.BuyListEndDate
for xml path('BuyList'), type
),
--pricecheck
( SELECT
PPC.RetailerName as "@CompetitorName",
CONVERT(DECIMAL(18,2),PPC.RetailerPrice) as "@CompetitorPrice",
CONVERT(VARCHAR(10),PPC.RetailerPriceCheckDate,120) as "@CheckDate"
FROM
integration.ProductPriceComparison PPC
WHERE
PPC.[CatalogueItemID] = CL.[CatalogueItemID]
for xml path('PriceCheck'), type
),
--BuyListSequence_
( SELECT
BL.[CatalogueItemBuyListSequenceNumber] as "@__Value",
'BuyListSequence_'+CONVERT(VARCHAR,BL.[BuyListID]) as "@__Name"
FROM
[integration].[BuyListProductRecipes] BL
WHERE
BL.[CatalogueItemID] = CL.[CatalogueItemID]
AND CURRENT_TIMESTAMP BETWEEN BL.BuyListStartDate AND BL.BuyListEndDate
for xml path('BuyListSequence'), type
),
--salesrank
( SELECT
PSR.[ProductSalesRank] as "@__Value"
FROM
[integration].[ProductSalesRank] PSR
WHERE
PSR.[CatalogueItemID] = CL.[CatalogueItemID]
for xml path('UnitsSold'), type
)
FROM
--Resultset may have a match on either BuyList, PriceCheck or SalesRank for the supplied CatalogueItemId's
(select
DISTINCT COALESCE(BL.[CatalogueItemID],PPC.[CatalogueItemID],PSR.[CatalogueItemID]) as [CatalogueItemID]
FROM
[integration].[BuyListProductRecipes] BL
FULL OUTER JOIN
integration.ProductPriceComparison ppc
on BL.[CatalogueItemID] = PPC.[CatalogueItemID]
FULL OUTER JOIN
[integration].[ProductSalesRank] PSR
on PSR.[CatalogueItemID] = PPC.[CatalogueItemID]
) CL --List of all CatalogueItems
INNER JOIN
@CatalogueItemTable CIT
ON CIT.[CatalogueItemID] = CL.[CatalogueItemID]
FOR XML PATH ('ProductInformationEnhancements'), type)
FOR XML PATH('ProductInformationEnhancementsDS')

--ErrorChecking
SELECT @RowCount = @@ROWCOUNT

--################################################## Post-Contract Check #########################################################################
--Post contract check of active rules - there must be 1 and only 1 row returned
IF (@RowCount = 0) BEGIN
SET @ErrorDescription = '### ERROR - DATA INTEGRITY FAILURE : Stored Procedure [integration].[GetProductInformationEnhancementsXML]'
+' Returned : ['+CONVERT(VARCHAR(16),@RowCount)+'] rows and is FAILING - '
+'ERROR ###'
RAISERROR (@ErrorDescription,16,1)
END

SET @Error = 0 --Success
END TRY

-- Open a Catch block handle any errors with severity > 10 that were caught by the try block above
BEGIN CATCH
SELECT @ErrorProcedure = Routine_Schema + '.' + Routine_Name,
@Error = ERROR_NUMBER(),
@ErrorDescription = ERROR_MESSAGE(),
@ErrorLine = ERROR_LINE()
FROM INFORMATION_SCHEMA.ROUTINES
WHERE Routine_Type = 'PROCEDURE' and Routine_Name = OBJECT_NAME(@@PROCID)

RAISERROR('[Procedure:%s Line:%i Error:%i] %s',16,1,@ErrorProcedure,@ErrorLine,@Error,@ErrorDescription)
END CATCH

--################################################## Procedure Code End ##############################################################################
RETURN @Error
END --Procedure

Post #313803

jack a hummer

Posted Wednesday, October 11, 2006 3:16 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 9:15 AM
Points: 20, Visits: 227

Nice use of XML for data manipulation. I have not tried anything like that yet. My next task is to try an XML-type column to hold a variable number of 'keywords' related to the row, plus experiment with an XML index for retrieval.

In terms of trade-offs between maintainability and performance, that can go either way depending on circumstances. In the systems I have developed over the years (mostly as an ISV), making maintenane easier always won.

The original industry-wide airline reservation system was very difficult to maintain, because they had to optimize the heck out of it to service tens of thousands of terminals and hundreds of TPS on the available computer power of the day. That's why airline codes are 2 and exactly 2 uppercase only alpha characters, airports are only 3 chars, etc. as they squeezed out every bit possible. It used a custom file system missing most of the niceties we come to expect from a robust RDBMS. Not a system I would like to maintain.

Post #314776

« Prev Topic | Next Topic »

Permissions