Как я могу написать XQuery для доступа к XML, хранящемуся в отдельных таблицах SQL Server?

Я создал новую базу данных с двумя таблицами для проверки функциональности SQLServer 2008 XML.

Если мне удастся добиться желаемого поведения, мне придется создать около 50 таблиц, а затем импортировать множество XML-файлов для нового проекта.

Моя тестовая среда была создана следующим образом:

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

create table GeoCountries(
    CountriesFeed xml
)
go

Затем я загрузил около 1000 xml-файлов в таблицу «Сотрудники» и 1 файл в таблицу «GeoCountries». Файл GeoCountries содержит координаты центроида широты и долготы для 249 стран, а также название страны и код страны, состоящий из 3 символов ISO.

У каждого сотрудника есть код страны. У меня уже был готовый XQuery в другом продукте, и теперь мне нужно перейти на SQL Server по требованию клиента.

Примеры данных из двух таблиц, полученных с помощью 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>
    ...
*/

Это запрос, который я пытаюсь запустить:

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="" />
    ...
*/

Как видите, коды стран + широта/долгота предварительно загружены в переменную $ccc, чтобы использовать ее в качестве таблицы поиска, но поскольку эти данные находятся в другой таблице SQL (GeoCountries), их нельзя найти в текущем контексте XQuery, который привязан к таблице SQL Employees.

Есть ли способ запустить XQuery для доступа к XML, хранящемуся в отдельных таблицах SQL? Если я мигрирую, у меня будет 100-200 подобных ситуаций, и мне нужно найти эффективное решение этой проблемы.


sql-server xquery xquery-sql
person MarioCannistra    schedule 21.02.2013    source источник
comment
Можете ли вы показать нам, как на самом деле выглядит отдельный XML-файл EmployeesFeed и ContriesFeed? Прямо сейчас вы показываете нам только восстановленный XML из запроса по многим экземплярам XML, поэтому неясно, как выглядят отдельные строки.   -  person RBarryYoung    schedule 21.02.2013

Ответы (1)


arrow_upward
1
arrow_downward

Как насчет этого:

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
person muhmud    schedule 21.02.2013
comment
Спасибо Мухмуд. Ваш подход работает, и я могу получить желаемый результат. Я просто беспокоюсь о накладных расходах с точки зрения сложности и количества кода, которые, по-видимому, необходимы для переключения с чистого xquery... По сути, здесь мы переходим к промежуточному набору данных sql, чтобы затем переключиться обратно на xml с помощью for xml . Есть ли способ ссылаться на xml в другой таблице, используя что-то вроде подхода fn: doc (tablename)? Мне также интересно, может ли этот подход привести к некоторой степени снижения производительности... - person MarioCannistra; 21.02.2013
comment
При необходимости вы можете создавать XML-индексы. Однако, если вы хотите Xquery, я думаю, вам придется получить биты xml в одном и том же месте либо путем объединения, либо, возможно, с помощью sqlvariable - msdn.microsoft.com/en-us/library/ms188254.aspx. Даже с этим вам все равно придется просмотреть большую часть вашего XML, но на этот раз в Xquery. Вы можете попытаться уменьшить это с помощью предложений where, но тогда вы также можете применить это к приведенному выше. - person muhmud; 21.02.2013
comment
У меня точно такая же проблема, и мне интересно, нашли ли вы способ сказать справочные данные xml из другой таблицы из XQuery (например, fn:doc(tablename) или fn:collection(collectionname)? Или это просто невозможно? А как насчет новых возможностей SQL Server 2014? Заранее спасибо. - person Adrian; 25.03.2014