CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
SBB
SBB

Reputation: 8970

TSQL XML Parsing on select statement

I am using a select statement to query some results from my database. One of those columns contains XML data that I need to access.

SELECT A.[id],
A.[empID],
A.[licensePlate],
A.[carColor],
A.[carModel],
 (
 SELECT  ParamValues.x2.value('empID[0]', 'VARCHAR(100)')
 FROM   A.[carPoolMembers].nodes('/carPool/employee') AS ParamValues(x2)  
 )
FROM   licensePlates as A
WHERE  empID = @empID
FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('results');

Here is what the XML looks like in that row: <carPool> <employee> <empID>123</empID> </employee> <employee> <empID>456</empID> </employee> </carPool>

I am getting this error when running the stored procedure: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any ideas on how to pull the XML from the row and parse it ?

Upvotes: 0

Views: 228

Answers (2)

Hart CO
Hart CO

Reputation: 34774

If you could just run the sub-select:

 SELECT  ParamValues.x2.value('empID[0]', 'VARCHAR(100)')
 FROM   A.[carPoolMembers].nodes('/carPool/employee')

It would return more than 1 row, which necessarily breaks the query.

Normally a sub-select can be correlated to the outer select, you just need to determine how to identify the specific value of the multiples returned that you want included in your outer-select.

Upvotes: 1

mwigdahl
mwigdahl

Reputation: 16578

You need to clarify what you want the resulting structure to be.

Assuming you want multiple rows with this data, one for each employee ID in the carPoolMembers XML block, you can do something like this:

SELECT A.[id],
A.[empID],
A.[licensePlate],
A.[carColor],
A.[carModel],
ParamValues.x2.value('empID[1]', 'VARCHAR(100)') AS carPoolEmpID
FROM   licensePlates as A
CROSS APPLY A.[carPoolMembers].nodes('/carPool/employee') AS ParamValues(x2)
WHERE  empID = @empID
FOR    XML PATH ('data'), TYPE, ELEMENTS, ROOT ('results');

This will only show rows that have at least one employee in the car pool. If you want to show all employees regardless of car pool settings, you can use OUTER APPLY instead.

Upvotes: 0

Related Questions