как сжать и запросить столбец xml, где узлы являются динамическими на сервере sql

У нас есть столбец xml, в котором имена узлов меняются от сайта к сайту (по сути, мы позволяем пользователям создавать настраиваемые поля в дополнение к стандартным полям, которые мы предоставляем, и эти поля хранятся в столбце xml, о котором я упоминал. Образец ячейки выглядит как

<Dictionary><UnitNumber>56</UnitNumber><AptNo>12</AptNo></Dictionary>

Обратите внимание, что узлы UnitNumber и AptNo полностью зависят от клиента, это могут быть AutoBrand, ManifestNo и TravelSite.

Теперь мы позволяем клиентам делать запросы по этим полям. Сохраненная процедура получает параметр xml, и мы анализируем этот параметр xml во временную таблицу, таблица выглядит так

field         value
-----          -----
UnitNumber      56
AptNo           12

Итак, эта таблица - это то, что клиент передал в качестве предложения where (которое должно быть переведено на select * from t, где UnitNumber = 56 и AptNo = 12)

Затем мы удалили столбец xml из таблицы, из которой будут получены результаты. Поскольку мы используем функцию nodes () и OUTER APPLY, в конечном CTE дублируются некоторые идентификаторы, поскольку у них было более одного узла.

Оператор sql, в котором мы выполняем измельчение xml, выглядит следующим образом

SELECT T.C.value('local-name(.)', 'nvarchar(100)') as CustomFieldName,
T.C.value('.', 'nvarchar(100)') as CustomFieldValue,
#tempTable.*
FROM #tempTable
OUTER APPLY #tempTable.CustomFields.nodes('/Dictionary/child::*') as T(C)

Это возвращает что-то вроде

CustomFieldName        CustomFieldValue       EntityId
---------------        ----------------       ----------
UnitNumber               56                     1
AptNo                    12                     1
UnitNumber               56                     2

Итак, когда мы присоединяемся к CTE с временной таблицей, мы присоединяемся к

CustomFieldName = field and CustomFieldValue = value

И запрос возвращает как entityId 1, так и entityId 2, но я хочу, чтобы он возвращал только entityId 1, потому что это единственная сущность, которая удовлетворяет обоим условиям

(UnitNumber = 56 AND AptNo = 12)

Я не придумал, как этого добиться. Конечно, было бы очень полезно, если бы способ хранения CustomFields был структурирован, но это устоявшаяся система, и сейчас ее нельзя изменить.

Любые советы приветствуются. Спасибо!


sql-server tsql sqlxml
person freud    schedule 12.06.2013    source источник
comment
Отличный вопрос. Спасибо за публикацию, с нетерпением жду ответов.   -  person Bill Gregg    schedule 13.06.2013

Ответы (2)


arrow_upward
0
arrow_downward

Что-то вроде этого могло бы вас приблизить. С верхней части моей головы:

Select top 1 EntityId from #cte 
left join #tempTable 
on CustomFieldName = field 
and CustomFieldValue = value 
group by EntityId 
order by count(entityid) desc
person rivarolle    schedule 12.06.2013
comment
Все еще пытаюсь понять, как использовать соглашения stackoverflow, но хотел бы поблагодарить вас за ваш ответ. Это было очень полезно. - person freud; 13.06.2013

arrow_upward
0
arrow_downward

Это действительно приблизило меня. Вот что я в итоге сделал

SELECT ROW_NUMBER() OVER(PARTITION BY EntityId ORDER BY EntityId DESC) As EntityCount, EntityId
from CTE JOIN #tempTable ON 
CustomFieldName = field and CustomFieldValue = value 
Where EntityCount = (select count(*) from #temptable)

Спасибо за помощь.

person freud    schedule 13.06.2013