Query XML parameter in SQL

Question

This seemed like a great idea on Friday afternoon but I'm having a bit of trouble. I've not used SQL XML querying before so I may of just done something incredibly stupid. Basically I want to pass a series of strings to query a table into a Stored Procedure.

I thought about this for a bit, considered using a CSV and then decided to attempt to do this using XML. So My XML looks like:

<Root>
  <string>value</string>
  <string>value</string>
  <string>value</string>
  <string>value</string>
</Root>

I'm passing this into a stored proc as an XML value type:

CREATE PROCEDURE usp_UpdateHotelImages
    -- Add the parameters for the stored procedure here
    @hotelID int, 
    @imageIDs xml
AS
BEGIN

so I want to shred the XML into a table of strings.

My SQL looks like this:

SELECT Child.value('(string)[1]', 'varchar(200)')
FROM @imageIDs.nodes('/Root/') AS N(Child))

But I keep getting the error message XQuery [nodes()]: Syntax error near '<eof>', expected a "node test".

I may well be doing something incredibly stupid here so any help will be gratefully received.

Update

I've broken it down into a single query to help:

DECLARE @imageIDs xml
SET @imageIDs = '<Root>
  <string>value</string>
  <string>value</string>
  <string>value</string>
  <string>value</string>
</Root>'

SELECT Child.value('(string)[1]', 'varchar(200)')
        FROM @imageIDs.nodes('/Root/') AS N(Child)

podiluska · Accepted Answer · 2012-10-01 08:41:09Z

2

The problem is the last / in the nodes function.

SELECT Child.value('(string)[1]', 'varchar(200)') 
FROM @imageIDs.nodes('/Root') AS N(Child)

or alternatively

SELECT Child.value('(.)[1]', 'varchar(200)') 
FROM @imageIDs.nodes('/Root/*') AS N(Child)

Depending on what you're trying to achieve.

answered Oct 1, 2012 at 8:41

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liam Over a year ago

That's helped, no longer getting exception but it only returns a single row?

Vikdor · Accepted Answer · 2012-10-01 08:42:51Z

1

The error is because of trailing / in your nodes expressions. It should just be /Root.

BTW, I think you are looking for a SELECT to return the values as a table which is achieved by the following:

DECLARE @imageIDs XML

SELECT @imageIDs = '
<Root>
  <string>value</string>
  <string>value2</string>
  <string>value3</string>
  <string>value4</string>
</Root>'

SELECT 
    Child.value('(.)[1]', 'varchar(200)')
FROM @imageIDs.nodes('/Root/string') AS N(Child)

Results:

value
value2
value3
value4

(4 row(s) affected)

answered Oct 1, 2012 at 8:42

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Collectives™ on Stack Overflow

Query XML parameter in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related