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]

@CatalogueNumberListXML,--This XML is provided by the calling application, and contains a list of CatalogueItemIDs

@CatalogueNumberSelectXPathNVarChar(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

@ErrorDescriptionVARCHAR(512),

@ErrorINT,

@RowCountINT,

@XMLHandleINT,

@ErrorProcedureSYSNAME,

@ErrorLineINT

DECLARE

@CatalogueItemTableTABLE ([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])

SELECTCONVERT(NCHAR(8),[text])

FROMOPENXML (@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

NULLas "@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.RetailerNameas "@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()

FROMINFORMATION_SCHEMA.ROUTINES

WHERERoutine_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