XML Argument Protocols for SQL 2005 Stored Procedures

  • jack a hummer

    SSC Veteran

    Points: 220

    Comments posted here are about the content posted at temp

  • Regan Galbraith

    SSCertifiable

    Points: 5227

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

    --

    ################################################## 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:

    -- &lt ProductInformationEnhancementsDS>

    -- &lt ProductInformationEnhancements CatalogueItemID="1">

    -- &lt BuyList __Ref="BuyListID" BuyListID="15" BuyListName="Strata" BuyListSequence="2"/>

    -- &lt BuyList __Ref="BuyListID" BuyListID="20" BuyListName="Strata2" BuyListSequence="1"/>

    -- &lt PriceCheck CompetitorName="Acme" CompetitorPrice="8.99" CheckDate="2006-06-17"/>

    -- &lt PriceCheck CompetitorName="Acme2" CompetitorPrice="8.99" CheckDate="2006-06-17"/>

    -- &lt BuyListSequence __Value="2" __Name="BuyListSequence_15" />

    -- &lt BuyListSequence __Value="1" __Name="BuyListSequence_20" />

    -- &lt UnitsSold __Value="34"/>

    -- &lt /ProductInformationEnhancements>

    -- &lt ProductInformationEnhancements CatalogueItemID="2">

    -- &lt PriceCheck CompetitorName="Acme" CompetitorPrice="5.00" CheckDate="2006-06-17"/>

    -- &lt /ProductInformationEnhancements>

    -- &lt ProductInformationEnhancements CatalogueItemID="3">

    -- &lt PriceCheck CompetitorName="Acme" CompetitorPrice="67.00" CheckDate="2006-06-17"/>

    -- &lt /ProductInformationEnhancements>

    -- &lt ProductInformationEnhancements CatalogueItemID="4">

    -- &lt PriceCheck CompetitorName="Acme2" CompetitorPrice="78.00" CheckDate="2006-06-17"/>

    -- &lt /ProductInformationEnhancements>

    -- &lt /ProductInformationEnhancementsDS>

    --ToDo:

    --Sample Usage:

    --DECLARE @RC int

    --DECLARE

    -- @CatalogueNumberList xml,

    -- @CatalogueNumberSelectXPath nvarchar(255)

    --

    --set @CatalogueNumberList = '&lt ProductInformationEnhancementsRequest DataViewType="Raw" >

    --&lt CatalogueNumbers>

    -- &lt CatalogueNumber>1 &lt /CatalogueNumber>

    -- &lt CatalogueNumber>2 &lt /CatalogueNumber>

    -- &lt CatalogueNumber>3 &lt /CatalogueNumber>

    -- &lt CatalogueNumber>4 &lt /CatalogueNumber>

    --&lt /CatalogueNumbers>

    --&lt /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

  • jack a hummer

    SSC Veteran

    Points: 220

    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.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply