• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Changing this stored proc example that uses XML

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx


The XML being passed to it has an "ID" node. I want to add another field, "ID2", but not sure how to change that select statement to
include the second node

create procedure test (@productIds xml)
as

DECLARE @Products TABLE (ID INT, ID2 VARCHAR(2))

 INSERT INTO @Products (ID, ID2)
  SELECT ParamValues.ID.value('.','VARCHAR(20)'), ParamValues.ID2.value('.','VARCHAR(20)')
   FROM @productIds.nodes('/Products/id') as ParamValues(ID) -- how to change this to select the second node
  
  SELECT * FROM @Products

Open in new window


passing this

EXEC   test @productIds='<Products><id>3</id><id2>AA</id2><id>6</id><id2>BB</id2><id>15</id><id2>CC</id2></Products>'
0
Camillia
Asked:
Camillia
  • 2
1 Solution
 
TempDBACommented:
Try changing your xml to be simpler and I will leave it for you to try and get the result :)

EXEC   test @productIds='<Products><idSet><id>3</id><id2>AA</id2></idSet><idSet><id>6</id><id2>BB</id2></idSet><idSet><id>15</id><id2>CC</id2></idSet></Products>'
0
 
CamilliaAuthor Commented:
let me try
0
 
TempDBACommented:
Thanks. All the best with your project!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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