Using your attached example you would modify the XPath Expression like so

*:StaffingOrder/*:StaffingPosition/*:CustomFields/*:CustomField[@name="Reason for Request"]/*:pickList/*:item'

Here's the full solution:

DECLARE @x XML =

'<?xml version="1.0" encoding="UTF-8"?>

<StaffingOrder xmlns="jobPosting" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="jobPosting https://xuat.fgvms.com/schema/InSiteJobPostingV2.xsd">

<OrderId validFrom="12/31/2015"...

IF you want to pass columns to a table valued function like that you need to use APPLY. I suspect that this will work for you:

SELECT RigsVolTable

, Try_convert(float,...

Can you post some DDL and the execution plan?

tt-615680 (12/16/2015)

---

SET NOCOUNT ON;

Exec sp_MSforeachdb 'USE ?

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

SET @dbid...

Cool. On a side note for future reference about UNION... UNION ALL pretty...

John more-or-less beat me to it and drews solution is probably better but here's what I came up with. Using an inline table valued function you could do this:

USE tempdb

GO

--...

MAXDOP restricts the number of CPUs that can be used in a parallel query plan. MAXDOP 0 let's SQL Server use as many as SQL Server wants (up to 64...

Thanks Phil...

And since I had a few minutes to kill during lunch, a set-based solution that uses no XML...

-- declaring as varchar for...

another way...

DECLARE @x XML = '<?xml version="1.0" encoding="UTF-8"?>

<StaffingOrder>

<OrderId validFrom="12/31/2015" validTo="12/31/2015">

<IdValue>JNJNJP00040440</IdValue>

<Status>Submitted</Status>

</OrderId>

<StaffingPosition>

<CustomFields>

...

Great article!

paul.kemner (12/15/2015)

---

The flip side is that a lot of consulting companies will grossly oversell your skillset to get a paying contract. They'll sell you as an expert in xyz language,...

Quality experience is always better than quantity. I have interviewed a lot of people who, on paper, looked pretty good but after 30 minutes I had no idea what they...

Gazareth (12/14/2015)

---

No it isn't. 1st one returns contents of...

<removed>... Posted twice somehow.

I have never seen that but it's possible to have a column named <blank space>. Note the DDL below:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;

CREATE TABLE #t1 ([ ]...