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, Feb 21 '13 at 14:38

score 1 · Answer 1 · answered Feb 21 '13 at 14:44

1

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

answered Feb 21 '13 at 14:44

muhmud

4,474
2
15
22

Thank you muhmud. Your approach works and I can get the desired output. I'm just worried for the overhead in terms of code complexity and quantity that seems needed to switch from a pure xquery... Basically, here we are going to an intermediate set of sql data to then switch back to xml with the "for xml". Is there a way to reference xml in the other table using something like an fn:doc(tablename) approach ? I'm also wondering if this approach can introduce some degree of performance degradation... – MarioCannistra Feb 21 '13 at 15:26
You can create XML indexes if need be. However, if you want to Xquery, I think you're going to have to get the bits of xml in the same place either by concatenating or maybe with sqlvariable - http://msdn.microsoft.com/en-us/library/ms188254.aspx. Even with this you still need to go through most of your XML, but this time in Xquery. You could try to reduce this with where clauses, but then you could also apply that to the above. – muhmud Feb 21 '13 at 18:42
I have the exact same problem and I was wondering if you did find a way to say "reference xml data from another table from within XQuery (e.g. fn:doc(tablename) or fn:collection(collectioname)? Or is this simply not possible? What about the new capabilities of SQL Server 2014? Thanks in advance. – Adrian Mar 25 '14 at 09:07

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

1 Answers1