How can I write an XQuery accessing XML stored in separate SQL Server tables? -

How can I write an XQuery accessing XML stored in separate SQL Server tables? -

i have created new database 2 tables test sqlserver 2008 xml functionality.

if succeed in obtaining desired behaviour have create 50 tables import lots of xml files new project.

my test environment has been created way:

create table employees( idemployeefeed bigint primary key identity(1,1), employeefeed xml ) go create table geocountries( countriesfeed xml ) go

i loaded 1000 xml files in employees table , 1 file in geocountries table. geocountries file contains lat/long centroid coordinates 249 countries plus country name , iso 3 chars country code.

each of employee has country code. had xquery ready in product , have need migrate sql server per client requirement.

sample info 2 tables obtained xquery:

select employeefeed.query('//employee') employee employees /* output: <employee empid="1111" > <displayname>john</displayname> <country code="usa" /> </employee> <employee empid="2222" > <displayname>mario</displayname> <country code="ita" /> </employee> ... */ select employeefeed.query('//employee/country') employee employees /* output: <country code="usa" /> <country code="ita" /> ... */ select countriesfeed.query('//country') geocountries /* output: <country> <iso3166a3>usa</iso3166a3> <isoen_name>united states</isoen_name> <latitude>38.000</latitude> <longitude>-97.000</longitude> </country> <country> <iso3166a3>ita</iso3166a3> <isoen_name>italy</isoen_name> <latitude>42.833</latitude> <longitude>12.833</longitude> </country> ... */ select countriesfeed.query('//country/iso3166a3') geocountries /* output: <iso3166a3>usa</iso3166a3> <iso3166a3>ita</iso3166a3> ... */

this query i'm attempting run:

select employeefeed.query(n' allow $ccc := //country allow $ttt := //employee $t in $ttt homecoming <geoemp empcode="{ $t/@empid }" countrycode="{ $t/country/@code }" latit="{ $ccc[iso3166a3 = $t/country/@code]/latitude/text() }" longit="{ $ccc[iso3166a3 = $t/country/@code]/longitude/text() }" /> ') geoemployees employees /* output: <geoemp empcode="1111" countrycode="usa" latit="" longit="" /> <geoemp empcode="2222" countrycode="ita" latit="" longit="" /> ... */

as can see, country codes + lat/long pre-loaded in variable $ccc utilize lookup table but, beingness info in sql table (geocountries), cannot found in current xquery context tied sql table employees.

is there way run xquery accessing xml stored in separate sql tables ? have 100-200 similar situations manage if migrate , need find efficient solution problem.

how this:

select e.empid '@empcode', c.code '@countrycode', c.lat '@latit', c.long '@longit' ( select e.emp.value('(@empid)[1]', 'int') empid, e.emp.value('(country/@code)[1]', 'varchar(32)') code employees cross apply employeefeed.nodes('//employee') e(emp) ) e inner bring together ( select c.country.value('(iso3166a3)[1]', 'varchar(32)') code, c.country.value('(latitude)[1]', 'varchar(32)') lat, c.country.value('(longitude)[1]', 'varchar(32)') long geocountries cross apply countriesfeed.nodes('//country') c(country) ) c on e.code = c.code xml path('geoemp'), type

sql-server xquery xquery-sql