October 6, 2006 at 6:24 am
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:
--< 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
@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
October 11, 2006 at 3:16 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply