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

Question

I have created a new database with two tables to test SQLServer 2008 XML functionality.

If I will succeed in obtaining the desired behaviour I will have to create about 50 tables to then import lots of XML files for a new project.

My test environment has been created this way:

create table Employees(
    idEmployeeFeed bigint primary key IDENTITY(1,1),
    EmployeeFeed xml
)
go

create table GeoCountries(
    CountriesFeed xml
)
go

I then loaded about 1000 xml files in the Employees table and 1 file in the GeoCountries table. The GeoCountries file contains lat/long centroid coordinates for 249 countries plus the country name and ISO 3 chars country code.

Each of the employee has a country code. I already had an XQuery ready in another product and I have now the need to migrate to SQL Server per client requirement.

Sample data from the two tables obtained with XQuery:

select EmployeeFeed.query('//employee') as employee
from 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') as employee
from Employees
/* output:
    <country code="USA" />
    <country code="ITA" />
    ...
*/

select CountriesFeed.query('//country')
from 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')
from GeoCountries
/* output:
    <ISO3166A3>USA</ISO3166A3>
    <ISO3166A3>ITA</ISO3166A3>
    ...
*/

This is the query that I'm attempting to run:

select EmployeeFeed.query(N'
  let $ccc := //country
  let $ttt := //employee
  for $t in $ttt  
  return
        <geoEmp
        empCode="{ $t/@empID }" 
        countryCode="{ $t/country/@code }" 
        latit="{ $ccc[ISO3166A3 = $t/country/@code]/latitude/text() }" 
        longit="{ $ccc[ISO3166A3 = $t/country/@code]/longitude/text() }"
        />
') as GeoEmployees
from Employees
/* output:
    <geoEmp empCode="1111" countryCode="USA" latit="" longit="" />
    <geoEmp empCode="2222" countryCode="ITA" latit="" longit="" />
    ...
*/

As you can see, the country codes + lat/long are pre-loaded in variable $ccc to use it as a lookup table but, being that data in another SQL table (GeoCountries), it cannot be found in the current XQuery context that is tied to SQL table Employees.

Is there a way to run an XQuery accessing XML stored in separate SQL tables ? I will have 100-200 similar situations to manage if I migrate and I need to find an efficient solution for this problem.

Can You show us what the actual individual EmployeesFeed and ContriesFeed XML looks like? Right now you are only showing us the reconstituted XML from a Query over many instances of the XML, so it's not clear what the individual rows look like. — RBarryYoung

muhmud muhmud · Accepted Answer · 2013-02-21T14:44:18

How about this:

select e.empID as '@empCode', c.code as '@countryCode', c.lat as '@latit', c.long as '@longit' from (
    select e.emp.value('(@empID)[1]', 'int') as empID, e.emp.value('(country/@code)[1]', 'varchar(32)') as code
    from Employees
    cross apply EmployeeFeed.nodes('//employee') e(emp)
) e
inner join (
    select c.country.value('(ISO3166A3)[1]', 'varchar(32)') as code, c.country.value('(latitude)[1]', 'varchar(32)') as lat, c.country.value('(longitude)[1]', 'varchar(32)') as long
    from GeoCountries
    cross apply CountriesFeed.nodes('//country') c(country)
) c on e.code = c.code
for xml path('geoEmp'), type

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

1 Answers