Changing this stored proc example that uses XML

Posted on 2012-09-13
Last Modified: 2012-09-17

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)


 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>'
Question by:Camillia
    LVL 25

    Accepted Solution

    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>'
    LVL 7

    Author Comment

    let me try
    LVL 25

    Expert Comment

    Thanks. All the best with your project!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Another way of doing this SQL 8 32
    if and else in stored procedure 19 31
    Sql Data via Excel--performance issues 2 39
    report c# 9 62
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now