SQL Server - subquery with xmlnamespaces and cross apply

I want to use the results from the following to join to other tables:

;with xmlnamespaces ('http://thatonecompany.com/rest/model' as ns1)
 select id,
        isnull(null, cast(substring(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'), 24, len(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'))) as integer)) loc_id
   from MyOldXMLTable mo
   cross apply mo.x.nodes('/ns1:Entities/ns1:Entity/ns1:Attribute') as p(col)
where p.col.value('@name', 'nvarchar(max)') = 'foundTheRightOne'
  and mo.id < 10

Results are:

+----+--------+
| id | loc_id |
+----+--------+
|  3 |     47 |
|  4 |     47 |
|  5 |     47 |
|  6 |     47 |
|  7 |     47 |
|  8 |     47 |
|  9 |     47 |
+----+--------+

I was hoping I could do something like:

select * from (
;with xmlnamespaces ('http://thatonecompany.com/rest/model' as ns1)
 select id,
        isnull(null, cast(substring(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'), 24, len(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'))) as integer)) loc_id
   from MyOldXMLTable mo
   cross apply mo.x.nodes('/ns1:Entities/ns1:Entity/ns1:Attribute') as p(col)
where p.col.value('@name', 'nvarchar(max)') = 'foundTheRightOne'
  and mo.id < 10 ) as aa

... and then join it, but nope. I keep getting syntax errors because of the ;WITH and the cross apply. Anyone know how I should be doing this?

Solution

WITH XMLNAMESPACES applies to the whole query (it is very convenient, declare the namespace(s) once and use them anywhere in the query). You could also use xquery/inline namespace declarations which they tend to get too verbose.

create table #myoldxmltable
(
    id int identity(1,1) primary key clustered,
    x xml
);

insert into #myoldxmltable(x)
values(N'<ns1:Entities xmlns:ns1="http://thatonecompany.com/rest/model">  
  <ns1:Entity>
      <ns1:Attribute name="foundTheRightOne">
        <ns1:Entity href="http://test123.com/abc/1234567" />
      </ns1:Attribute>
  </ns1:Entity>
  </ns1:Entities>
');

--simple derived/as
with xmlnamespaces ('http://thatonecompany.com/rest/model' as ns1)
select *
from 
(
 select id,
        isnull(null, cast(substring(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'), 24, len(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'))) as integer)) loc_id
   from #myoldxmltable mo
   cross apply mo.x.nodes('/ns1:Entities/ns1:Entity/ns1:Attribute') as p(col)
where p.col.value('@name', 'nvarchar(max)') = 'foundTheRightOne'
  and mo.id < 10
) as aa;


--join with another table
with xmlnamespaces ('http://thatonecompany.com/rest/model' as ns1)
select *
from 
(
 select id,
        isnull(null, cast(substring(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'), 24, len(p.col.value('(./ns1:Entity/@href)[1]', 'nvarchar(max)'))) as integer)) loc_id
   from #myoldxmltable mo
   cross apply mo.x.nodes('/ns1:Entities/ns1:Entity/ns1:Attribute') as p(col)
where p.col.value('@name', 'nvarchar(max)') = 'foundTheRightOne'
  and mo.id < 10
) as aa
join sys.columns as o on aa.id = o.column_id and o.object_id = 3; --sql2019 , abysmal execution !!??!!


--a different flavor, xquery namespace declaration (way too verbose)
--compare this with the query using xmlnamespaces
select *
from 
(
 select id,
        isnull(null, cast(substring(p.col.value('declare namespace ns1="http://thatonecompany.com/rest/model";(./ns1:Entity/@href)[1]', 'nvarchar(max)'), 24, len(p.col.value('declare namespace ns1="http://thatonecompany.com/rest/model"; (./ns1:Entity/@href)[1]', 'nvarchar(max)'))) as integer)) loc_id
   from #myoldxmltable mo
   cross apply mo.x.nodes('
   declare namespace ns1="http://thatonecompany.com/rest/model";  
   /ns1:Entities/ns1:Entity/ns1:Attribute') as p(col)
where p.col.value('@name', 'nvarchar(max)') = 'foundTheRightOne'--no need for ns1 declaration, path just references an attribute, no ns1
  and mo.id < 10
) as aa;


drop table #myoldxmltable;