How to Select xml nodes SQL Server

Question

I have this sql script

DECLARE @XmlStr XML 
SET @XmlStr = '<EmployeeID>
<Employee>48f9194f-8d46-e111-8849-0050569445f1</Employee> 
<Employee>7d725561-8d46-e111-8849-0050569445f2</Employee> 
<Employee>7d725562-8d46-e111-8849-0050569445f3</Employee> 
<Employee>7d725563-8d46-e111-8849-0050569445f4</Employee> 
</EmployeeID>' 

(SELECT 
    @XmlStr.value('(/EmployeeID//Employee/node())[1]', 'UNIQUEIDENTIFIER') as IDCode 
    FROM @XmlStr.nodes('//EmployeeID') Tab(Col))

but when I run it result is:

IDCode

48F9194F-8D46-E111-8849-0050569445F1

(1 row(s) affected)

I want to select all four guids how can I achieve this?

my sql server version is 10.0.5500.0

Royi Namir · Accepted Answer · 2012-03-20 15:13:52Z

3

try this:

(SELECT 
    Col.value('.', 'UNIQUEIDENTIFIER') as IDCode 
    FROM @XmlStr.nodes('/EmployeeID/Employee') Tab(Col))

also : try to avoid using // in xpath

it is good for some specific cases.

answered Mar 20, 2012 at 15:13

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

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

Collectives™ on Stack Overflow

How to Select xml nodes SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related