Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    22

    Question Answered: XML values into #temp table

    Sorry if this is a bit long-winded, but could someone help me with a Stored Procedure, please?

    Using SQL 2008 R2 I'm trying to write a stored procedure that will create a #temp table and insert data (values) from a XML string into the #temp table.

    As far as I can tell - looking at examples through a Google search - the code I've written looks right but I'm getting the following error message...

    Code:
    Msg 174, Level 15, State 1, Procedure SP_Product_Transfer, Line 25
    The value function requires 2 argument(s).
    Here's my SP code...
    Code:
    USE [SuiteBaseDB]
    GO
    /****** Object:  StoredProcedure [dbo].[SP_Product_Add]    Script Date: 08/23/2015 18:04:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    create Procedure [dbo].[SP_Product_Transfer]
    (@XML xml)--, @NewSubRiskID int output)
    
    as
    
    begin try
    
    	begin tran
    	
    		create table #NewProdDets
    		(code varchar(50),
    		name varchar(100),
    		stocked bit,
    		stkqty int,
    		cost decimal(18,2),
    		sell decimal (18,2),
    		minstkqty int,
    		active bit,
    		vat int
    		)
    
    
    			INSERT INTO #NewProdDets	*this row is highlighted when error msg clicked*
    			(code,
    			name,
    			stocked,
    			stkqty,
    			cost,
    			sell,
    			minstkqty,
    			active,
    			vat
    			)
    			SELECT
    			p.presult.value('@code varchar(50)'),
    			p.presult.value('@name','varchar(100)'),
    			p.presult.value('@stocked','bit'),
    			p.presult.value('@stkqty','int'),
    			p.presult.value('@costprice','decimal (18,2)'),
    			p.presult.value('@sellprice','decimal (18,2)'),
    			p.presult.value('@minqty','int'),
    			p.presult.value('@active','bit'),
    			p.presult.value('@vat','int')
    			FROM    
    			@xml.nodes('products/product') as p(presult)
    
    	COMMIT TRAN
    
    	RETURN 0
    
    END TRY
    
    
    BEGIN CATCH
    
    IF XACT_STATE() <> 0 ROLLBACK TRAN
    
    SELECT ERROR_MESSAGE(),ERROR_LINE()
    
    RETURN -1
    
    END CATCH
    And, if it help, a sample of the XML string...
    Code:
    <products>
       <product code="APO003      " name="APOLLO 3C" stocked="True" costprice="23.61" sellprice="36.45" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="APO014SIL   " name="APOLLO 14AC SILVER RAL 9006" stocked="False" costprice="32.33" sellprice="50.4" vat="1" stkqty="0" minqty="0" active="False"/>
       <product code="HUG006PAA+S " name="HUGO-i 6 GATS PAA M/BK + SLIDER" stocked="True" costprice="67.67" sellprice="111.15" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="LLZLANGBUBLU" name="LANGHAM BLUE MESH EXEC * B-UP *" stocked="True" costprice="35.3" sellprice="54" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="MGOWENBLUBUL" name="OWEN H/BK TWIN-LEVER BLUE*B-UP*+LADEN CASTORS" stocked="True" costprice="22.41" sellprice="43" vat="1" stkqty="0" minqty="0" active="False"/>
       <product code="MOL310+PUL  " name="MOLOKO-s 310 GTS M/B SLIDER + PUMP UP LUMBAR" stocked="False" costprice="70.45" sellprice="126" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="SIS822SDA+PO" name="SISQO-i 822 GATS SDA KID X-H/BK+POLISHED BASE" stocked="True" costprice="54.402" sellprice="99.85" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="SPI026FAA+PU" name="NEW SPICE 26 GATS FOLD ADJ ARMS + PUL" stocked="False" costprice="41.628" sellprice="88.93" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="SPI190OLD+PU" name="SPICE 190 GS HIGH BACK + PUMP UP LUMBAR" stocked="True" costprice="27.828" sellprice="63" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="SPR323LINK  " name="SPRITE 323 BLACK LINKING SET ONLY" stocked="True" costprice="2.5" sellprice="3.5" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="SPR325AWTC*R" name="SPRITE 325AWT CHRM FRM R-TAB SIT" stocked="True" costprice="34.3" sellprice="55.65" vat="1" stkqty="0" minqty="0" active="True"/>
       <product code="WOD129      " name="COBRA HIGH OUTER WOOD BK@100" stocked="True" costprice="4.62" sellprice="7.65" vat="1" stkqty="145" minqty="0" active="True"/>
       <product code="Y50918      " name="M-BK SYNC ALLOY(A817C-A-ARM) BLACK LEATH" stocked="True" costprice="43.14" sellprice="50.99" vat="1" stkqty="41" minqty="0" active="True"/>
       <product code="Y974STRA10  " name="1S 1000 D-UP STRAIGHT (B) 9006 FRM" stocked="True" costprice="45.63" sellprice="53.95" vat="1" stkqty="2" minqty="0" active="False"/>
       <product code="Y978A       " name="FIXING BRACKETS FOR DESK MOUNT SCREENS" stocked="True" costprice="61.99" sellprice="99.99" vat="1" stkqty="-2" minqty="0" active="True"/>
    </products>
    Thanks in advance.

  2. Best Answer
    Posted by Pat Phelan

    "Based on https://msdn.microsoft.com/en-us/library/ms178030.aspx you can see that the Microsoft SQL Server Value() method for XML requires two arguments.

    This implies that the highlighted line in your original procedure is syntactically incorrect, and my suggested fix is:
    Code:
    USE [SuiteBaseDB]
    GO
    /****** Object:  StoredProcedure [dbo].[SP_Product_Add]    Script Date: 08/23/2015 18:04:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    create Procedure [dbo].[SP_Product_Transfer]
    (@XML xml)--, @NewSubRiskID int output)
    
    as
    
    begin try
    
    	begin tran
    	
    		create table #NewProdDets
    		(code varchar(50),
    		name varchar(100),
    		stocked bit,
    		stkqty int,
    		cost decimal(18,2),
    		sell decimal (18,2),
    		minstkqty int,
    		active bit,
    		vat int
    		)
    
    
    			INSERT INTO #NewProdDets	*this row is highlighted when error msg clicked*
    			(code,
    			name,
    			stocked,
    			stkqty,
    			cost,
    			sell,
    			minstkqty,
    			active,
    			vat
    			)
    			SELECT
    			p.presult.value('@code', 'varchar(50)'),
    			p.presult.value('@name','varchar(100)'),
    			p.presult.value('@stocked','bit'),
    			p.presult.value('@stkqty','int'),
    			p.presult.value('@costprice','decimal (18,2)'),
    			p.presult.value('@sellprice','decimal (18,2)'),
    			p.presult.value('@minqty','int'),
    			p.presult.value('@active','bit'),
    			p.presult.value('@vat','int')
    			FROM    
    			@xml.nodes('products/product') as p(presult)
    
    	COMMIT TRAN
    
    	RETURN 0
    
    END TRY
    
    
    BEGIN CATCH
    
    IF XACT_STATE() <> 0 ROLLBACK TRAN
    
    SELECT ERROR_MESSAGE(),ERROR_LINE()
    
    RETURN -1
    
    END CATCH
    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Based on https://msdn.microsoft.com/en-us/library/ms178030.aspx you can see that the Microsoft SQL Server Value() method for XML requires two arguments.

    This implies that the highlighted line in your original procedure is syntactically incorrect, and my suggested fix is:
    Code:
    USE [SuiteBaseDB]
    GO
    /****** Object:  StoredProcedure [dbo].[SP_Product_Add]    Script Date: 08/23/2015 18:04:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    create Procedure [dbo].[SP_Product_Transfer]
    (@XML xml)--, @NewSubRiskID int output)
    
    as
    
    begin try
    
    	begin tran
    	
    		create table #NewProdDets
    		(code varchar(50),
    		name varchar(100),
    		stocked bit,
    		stkqty int,
    		cost decimal(18,2),
    		sell decimal (18,2),
    		minstkqty int,
    		active bit,
    		vat int
    		)
    
    
    			INSERT INTO #NewProdDets	*this row is highlighted when error msg clicked*
    			(code,
    			name,
    			stocked,
    			stkqty,
    			cost,
    			sell,
    			minstkqty,
    			active,
    			vat
    			)
    			SELECT
    			p.presult.value('@code', 'varchar(50)'),
    			p.presult.value('@name','varchar(100)'),
    			p.presult.value('@stocked','bit'),
    			p.presult.value('@stkqty','int'),
    			p.presult.value('@costprice','decimal (18,2)'),
    			p.presult.value('@sellprice','decimal (18,2)'),
    			p.presult.value('@minqty','int'),
    			p.presult.value('@active','bit'),
    			p.presult.value('@vat','int')
    			FROM    
    			@xml.nodes('products/product') as p(presult)
    
    	COMMIT TRAN
    
    	RETURN 0
    
    END TRY
    
    
    BEGIN CATCH
    
    IF XACT_STATE() <> 0 ROLLBACK TRAN
    
    SELECT ERROR_MESSAGE(),ERROR_LINE()
    
    RETURN -1
    
    END CATCH
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2005
    Posts
    22
    Thanks for that. I knew I wasn't far away.

    Something so simple. I couldn't see it for looking.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •