Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

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

0
yetti78
Asked:
yetti78
  • 2
1 Solution
 
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
 
Chris LuttrellSenior 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now