help with xml stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with xml stored procedure

Author

Topic

ajoebs03
Starting Member

3 Posts

Posted - 2010-05-29 : 03:38:50

The code below returns: Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@orderNeoVal".

But @orderNeoVal is already declared.
Please help.

DECLARE @orderNeoVal xml
SET @orderNeoVal ='<root><old_data>
<consignee>
<id>1</id>
<name>WESTGATE</name>
<address1>25F, TOWER 2, ILCC, INSULAR LIFE DRIVE,</address1>
<address2>FILINVEST CORPORATE CITY, ALABANG MUNTINLUPA CITY,</address2>
<address3>PHILIPPINES</address3>
</consignee>
<buyer>
<id>1</id>
<name>BHA GROUP INC.</name>
<address1>8800 E 63RD ST.</address1>
<address2>KANSAS CITY, MO 64133,</address2>
<address3>USA</address3>
</buyer>
</old_data>
<new_data>
<consignee>
<id>1</id>
<name>WESTGATE123</name>
<address1>25F, TOWER 2, ILCC, INSULAR LIFE DRIVE,123</address1>
<address2>FILINVEST CORPORATE CITY, ALABANG MUNTINLUPA CITY,123</address2>
<address3>PHILIPPINES123</address3>
</consignee>
<buyer>
<id>1</id>
<name>BHA GROUP INC.123</name>
<address1>8800 E 63RD ST.123</address1>
<address2>KANSAS CITY, MO 64133,123</address2>
<address3>USA123</address3>
</buyer>
</new_data>
</root>'

DECLARE @PEOPLE AS TABLE ([data] XML )
INSERT INTO @PEOPLE SELECT
'<root><old_data>
<consignee>
<id>1</id>
<name>WESTGATE</name>
<address1>25F, TOWER 2, ILCC, INSULAR LIFE DRIVE,</address1>
<address2>FILINVEST CORPORATE CITY, ALABANG MUNTINLUPA CITY,</address2>
<address3>PHILIPPINES</address3>
</consignee>
<buyer>
<id>1</id>
<name>BHA GROUP INC.</name>
<address1>8800 E 63RD ST.</address1>
<address2>KANSAS CITY, MO 64133,</address2>
<address3>USA</address3>
</buyer>
</old_data>
<new_data>
<consignee>
<id>1</id>
<name>WESTGATE123</name>
<address1>25F, TOWER 2, ILCC, INSULAR LIFE DRIVE,123</address1>
<address2>FILINVEST CORPORATE CITY, ALABANG MUNTINLUPA CITY,123</address2>
<address3>PHILIPPINES123</address3>
</consignee>
<buyer>
<id>1</id>
<name>BHA GROUP INC.123</name>
<address1>8800 E 63RD ST.123</address1>
<address2>KANSAS CITY, MO 64133,123</address2>
<address3>USA123</address3>
</buyer>
</new_data>
</root>'

DECLARE @TEMP AS TABLE ([parent] NVARCHAR(100), [localname] NVARCHAR(100) )
INSERT INTO @TEMP
SELECT DISTINCT
--T.n.value('namespace[1]', 'varchar(100)') AS Namespace,
(T.n.value('parent[1]', 'NVARCHAR(100)')) AS parent,
T.n.value('localname[1]', 'NVARCHAR(100)') AS localname

FROM ( SELECT [data].query('
for $node in /descendant::node()[local-name() != ""]
return <node>
<namespace>{ namespace-uri($node) }</namespace>
<localname>{ local-name($node) }</localname>
<parent>{ local-name($node/..) }</parent>
</node>') AS nodes
FROM @PEOPLE
) q1
CROSS APPLY q1.nodes.nodes('/node') AS T ( n )

DECLARE @cnt1 INT
DECLARE @cnt2 INT
DECLARE @x1 INT = 0
DECLARE @x2 INT = 0
DECLARE @x3 INT = 0

SELECT @cnt1 = COUNT(DISTINCT parent) FROM @TEMP WHERE parent NOT IN ('', 'root','old_data','new_data')
SELECT @cnt2 = COUNT(DISTINCT localname) FROM @TEMP WHERE localname NOT IN (SELECT DISTINCT parent FROM @TEMP)

DECLARE @TEMP2 AS TABLE ([no] INT IDENTITY, [parent] NVARCHAR(100), [localname] NVARCHAR(100) )
INSERT INTO @TEMP2
SELECT DISTINCT
[parent], [localname]
FROM @TEMP WHERE parent NOT IN ('', 'root','old_data','new_data')

DECLARE @node NVARCHAR(50)
DECLARE @SqlCommand NVARCHAR(4000)
WHILE @x1 < @cnt1
BEGIN
SET @x1 = @x1 + 1
WHILE @x2 < @cnt2
BEGIN
SET @x2 = @x2 + 1
SET @x3 = @x3 + 1
SET @node = '/root/new_data/' + (SELECT [parent] FROM @TEMP2 WHERE [no] = @x3) + '/' + (SELECT [localname] FROM @TEMP2 WHERE [no] = @x2)
SET @SqlCommand = 'SELECT ParamValues1.ID.value(".","NVARCHAR(50)") as name FROM @orderNeoVal.nodes('''+@node+''') as ParamValues1(ID)'
PRINT @SqlCommand
EXEC (@SqlCommand)
END
SET @x2 = 0
END

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-30 : 03:12:13

You have declare @orderNeoVal outside the dynamic sql and you are trying to access it in dynamic sql. Thats the reason you are getting error.

One way to handle such requirement is to use sp_executesql statement.

Example:
exec sp_executesql @SqlCommand,N'@orderNeoVal as XML',@orderNeoVal

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-30 : 03:21:52

1) What are you trying to do?
2) Why are you using dynamic sql?

N 56°04'39.26"
E 12°55'05.63"

ajoebs03
Starting Member

3 Posts

Posted - 2010-05-30 : 22:36:31

@pk_bohra
When I use : exec sp_executesql @SqlCommand,N'@orderNeoVal as XML',@orderNeoVal

I get error : Msg 207, Level 16, State 1, Line 1
Invalid column name '.'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NVARCHAR(50)'.
Msg 207, Level 16, State 1, Line 1
Invalid column name '.'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NVARCHAR(50)'.
Msg 207, Level 16, State 1, Line 1
Invalid column name '.'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NVARCHAR(50)'.

@peso
1) I am trying to update the values of the xml @orderNeoVal to the sql table.
2) I am using dynamic sql because the value of @orderNeoVal changes based on whatever is updated in my application. Also "'SELECT ParamValues1.ID.value(".","NVARCHAR(50)") as name FROM @orderNeoVal.nodes('''+@node+''') as ParamValues1(ID)" only receives only string literals fo the .nodes(). The value of @node looks like this /root/new_data/buyer/address1 or /root/new_data/buyer/name or /root/new_data/consignee/id. Depends whatever is updated in the application.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources