Performing Multiple Merges in SQL Server 2008 Stored Procedure With Shredded XML

Okay,

1. An XML fragment that comes into a stored procedure in SQL Server 2008 Enterprise

2. The SP creates CTE representations of various elements of the XML using SQL node stuff

3. The proc then needs to insert/update on multiple tables based on the data in the CTE's using MERGE
 
I have the proc working perfectly when working with one table (MERGE), but can't get the second table to work nice. I am positive it is a formatting issue of the SP.

Help!

Code below with plenty to (hopefully) paint the picture
CREATE PROC [dbo].[zzz_some_sp]
(
	@inputData XML
)
AS

SET NOCOUNT ON
;

WITH XMLNAMESPACES ('http://www.fooinc.com' AS foo), 

_items AS (
	SELECT	items.item.value('(@foo:listingID)[1]', 'int') AS itemID,
		items.item.value('(title)[1]', 'varchar(255)') AS title,
		items.item.value('(foo:statusType/text())[1]','int') AS statusType
	FROM	@inputData.nodes('/item') AS items(item)
),

_lots AS (
	SELECT	lot.value('(@foo:assetID)[1]', 'INT') AS lotID,
		lot.value('(@foo:title)[1]', 'VARCHAR(50)') AS name,	
		lot.value('(foo:elevation/text())[1]', 'DECIMAL(18,2)') AS elevation
	FROM	@inputData.nodes('/item/foo:assets/foo:asset[@foo:type=''lot'']') AS lots(lot)
)

-- ITEMS
MERGE	items AS i
USING	_items AS _i
ON		(i.itemID = id.itemID)

WHEN MATCHED THEN
	UPDATE SET
		-- Match a = a etc. ..
WHEN NOT MATCHED THEN
	-- Insert a etc.
/ ITEMS

-- lots
MERGE	lots AS mp
USING	_parcels AS pd
ON		(mp.id_lot = pd.id_lot)

WHEN MATCHED THEN
	UPDATE SET
	-- p = p etc.
WHEN NOT MATCHED THEN
	INSERT 
	-- p into ....

-- / lots


----------------------------------------------------

Testing the SP

DECLARE @inputData XML;

SET @inputData = '
<item foo:listingID="1325" xmlns:foo="http://www.fooinc.com"> 
	<title>Name of the item</title>
	<foo:someValue />
	<foo:anotherValue />

	<foo:assets>
        <foo:asset foo:type="lot" foo:title="Vacant Lot" foo:assetID="1745">
		<foo:elevation>123</foo:elevation>
		<foo:anotherValue />
        </foo:asset>
	</foo:assets>
</item>'

EXEC zzz_some_sp @inputData

Open in new window

yetti78Asked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
by break in 2, I mean to have the CTE for each merge statement as below.  If you do not need both the _lots and _items psudo tables in each merge you can get rid of the extraneous one.
HTH
Chris
CREATE PROC [dbo].[zzz_some_sp] 
( 
        @inputData XML 
) 
AS 
 
SET NOCOUNT ON 
; 
 
WITH XMLNAMESPACES ('http://www.fooinc.com' AS foo),  
 
_items AS ( 
        SELECT  items.item.value('(@foo:listingID)[1]', 'int') AS itemID, 
                items.item.value('(title)[1]', 'varchar(255)') AS title, 
                items.item.value('(foo:statusType/text())[1]','int') AS statusType 
        FROM    @inputData.nodes('/item') AS items(item) 
), 
 
_lots AS ( 
        SELECT  lot.value('(@foo:assetID)[1]', 'INT') AS lotID, 
                lot.value('(@foo:title)[1]', 'VARCHAR(50)') AS name,     
                lot.value('(foo:elevation/text())[1]', 'DECIMAL(18,2)') AS elevation 
        FROM    @inputData.nodes('/item/foo:assets/foo:asset[@foo:type=''lot'']') AS lots(lot) 
) 
 
-- ITEMS 
MERGE   items AS i 
USING   _items AS _i 
ON              (i.itemID = id.itemID) 
 
WHEN MATCHED THEN 
        UPDATE SET 
                -- Match a = a etc. .. 
WHEN NOT MATCHED THEN 
        -- Insert a etc. 
/ ITEMS 
 
WITH XMLNAMESPACES ('http://www.fooinc.com' AS foo),  
 
_items AS ( 
        SELECT  items.item.value('(@foo:listingID)[1]', 'int') AS itemID, 
                items.item.value('(title)[1]', 'varchar(255)') AS title, 
                items.item.value('(foo:statusType/text())[1]','int') AS statusType 
        FROM    @inputData.nodes('/item') AS items(item) 
), 
 
_lots AS ( 
        SELECT  lot.value('(@foo:assetID)[1]', 'INT') AS lotID, 
                lot.value('(@foo:title)[1]', 'VARCHAR(50)') AS name,     
                lot.value('(foo:elevation/text())[1]', 'DECIMAL(18,2)') AS elevation 
        FROM    @inputData.nodes('/item/foo:assets/foo:asset[@foo:type=''lot'']') AS lots(lot) 
) 
-- lots 
MERGE   lots AS mp 
USING   _parcels AS pd 
ON              (mp.id_lot = pd.id_lot) 
 
WHEN MATCHED THEN 
        UPDATE SET 
        -- p = p etc. 
WHEN NOT MATCHED THEN 
        INSERT  
        -- p into .... 
 
-- / lots 
 
 
---------------------------------------------------- 
 
Testing the SP 
 
DECLARE @inputData XML; 
 
SET @inputData = ' 
<item foo:listingID="1325" xmlns:foo="http://www.fooinc.com">  
        <title>Name of the item</title> 
        <foo:someValue /> 
        <foo:anotherValue /> 
 
        <foo:assets> 
        <foo:asset foo:type="lot" foo:title="Vacant Lot" foo:assetID="1745"> 
                <foo:elevation>123</foo:elevation> 
                <foo:anotherValue /> 
        </foo:asset> 
        </foo:assets> 
</item>' 
 
EXEC zzz_some_sp @inputData

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
the CTE is only in scope (can be referenced) in the first SQL statement after it (your MERGE for items)  You will need to break it in 2 making one to merge items and one to merge lots.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.