Использование XML с Transact-SQL

ProgramBox

Использование XML с Transact-SQL

Post author:admin
Запись опубликована:2 марта, 2023
Post category:Вопросы по программированию

#tsql #xml-parsing #sql-server-2008-r2

#tsql #синтаксический анализ XML #sql-server-2008-r2

Вопрос:

Я работаю в SQL Server 2008 R2. У меня около 20 баз данных с одинаковой структурой. Я не совсем уверен, где я ошибся в своем коде. Если бы кто-нибудь мог указать на мою ошибку или указать мне на несколько ресурсов по этому вопросу, это было бы оценено.

Код, который я запускаю, является:

 create table #temp 
([Client] varchar(100), [Language_Code] char(100))

exec sp_msforeachdb '
if ''?'' like ''%'' and exists(select * from ?.sys.tables t where t.name =  ''LicenceInfoes'')
begin
DECLARE @MyXML XML
Declare @lang_code char(100)

set @lang_code = ''(SELECT a.b.value''(''Laguages[1]'',''char(100)'')'' FROM @MyXML.nodes(''Licence'') a(b))''
insert into #temp select ''?'', @lang_code  
end
'

select * from #temp

drop table #temp

Конечным результатом является эта ошибка:

Сообщение 102, уровень 15, состояние 1, строка 7
Неправильный синтаксис рядом с ‘Laguages [1]’.

Желаемым результатом будет таблица с 2 столбцами, подобными этому:

 <DB_Name> <Language_Codes>
  Name1      en-GB, en-US

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

 DECLARE @MyXML XML

SET @MyXML = (select LicenceData from LicenceInfoes where Id='1001')

SELECT 
    a.b.value('Laguages[1]', 'char(100)') AS Language_Codes 
FROM 
    @MyXML.nodes('Licence') a(b)

и образец XML, хранящийся в одной из баз данных

 <Licence xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Clients />
  <LastUpdated>2014-06-03T15:59:46.9831585Z</LastUpdated>
  <CustomerId>9999999</CustomerId>
  <CustomerName>xxxxx</CustomerName>
  <ContactEmail>someone@somewhere.com</ContactEmail>
  <Type>Commercial</Type>
  <Languages>
    <Language>en-GB</Language>
    <Language>en-US</Language>
  </Languages>
  <DefaultLanguagePack>en-GB</DefaultLanguagePack>

Любая помощь здесь приветствуется.

Редактировать

Попробовав несколько вещей, я обнаружил, где находится ошибка.

Ошибка находится в переменной @myXML:

 SET @MyXML = ''(select LicenceData from t.LicenceInfoes)''

Изменение t.LicenceInfoes на ?..LicenceInfoes исправил проблему, с которой я столкнулся.

Ответ №1:

Помогает ли это?

 DECLARE @Tbl TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @tbl VALUES(1, '<Licence xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Clients />
  <LastUpdated>2014-06-03T15:59:46.9831585Z</LastUpdated>
  <CustomerId>9999999</CustomerId>
  <CustomerName>xxxxx</CustomerName>
  <ContactEmail>someone@somewhere.com</ContactEmail>
  <Type>Commercial</Type>
  <Languages>
    <Language>en-GB</Language>
    <Language>en-US</Language>
  </Languages>
  <DefaultLanguagePack>en-GB</DefaultLanguagePack></Licence>')

SELECT
    ID,
    XT.XC.value('(.)[1]', 'varchar(50)')
FROM 
    @Tbl
CROSS APPLY
    XmlContent.nodes('/Licence/Languages/Language') AS XT(XC)

Это приводит к выводу:

 ID  (No column name)
1   en-GB
1   en-US

1. это прекрасно работает как есть. Мне нужно выполнить поиск этих данных, которые хранятся в столбце таблицы LicenceInfoes. Я попытался добавить оператор select, заменяющий данные xml, и я получаю пустую таблицу в качестве вывода. XML имеет одинаковую структуру для всех баз данных, но может отличаться по мере добавления или удаления языков.

Ответ №2:

Я полагаю, что ваша ошибка возникает из-за неправильного написания Laguages[1] в строке 7. Измените его на Languages[1] , и он должен выдать правильный вывод.

Ответ №3:

Я думаю, это может помочь.

 exec sp_msforeachdb '
if ''?'' like ''%'' and exists(select * from ?.sys.tables t where t.name =  ''LicenceInfoes'')
begin
DECLARE @MyXML XML
Declare @lang_code char(100)
SET @MyXML = (select LicenceData from t.LicenceInfoes)
set @lang_code = (SELECT a.b.value(''Laguages[1]'',''char(100)'') FROM @MyXML.nodes(''Licence'') a(b))
insert into #temp select ''?'', @lang_code  
end'

Я не понимаю значения if ''?'' like ''%'' , но теперь запрос синтаксически корректен.

1. Я попробовал, и вот результат: Col1: DB_Name Col2: (ВЫБЕРИТЕ значение a.b.(‘Laguages[1]’,’char(100)’) ИЗ @myXML.nodes(‘Лицензия’) a(b))

2. @user3753561, я не вижу результата, но я обновил сообщение.

Ответ №4:

Попробовав несколько ваших вариантов, я получил следующую ошибку:

 Msg 208, Level 16, State 1, Line 7
Invalid object name 'LicenceInfoes'.

Я понял, что допустил ошибку в той части кода, где я установил переменную @myXML.

это было:

 SET @MyXML = (select LicenceData from LicenceInfoes)

добавление «? ..» перед именем таблицы исправило это.

 SET @MyXML = (select LicenceData from ?..LicenceInfoes)

В итоге я обновил свой код благодаря marc_s. Теперь это выглядит так:

 create table Tbl (Client varchar(max), Language_Code varchar(max))
exec sp_msforeachdb '
    if ''?'' like ''%'' and exists(select * from ?.sys.tables t where t.name =  ''LicenceInfoes'')

begin
SET QUOTED_IDENTIFIER ON

INSERT INTO Tbl

SELECT
    ''?'', 
    a.b.value(''(.)[1]'',''varchar(100)'') AS Language_Codes 
FROM ?..LicenceInfoes 
cross apply LicenceData.nodes(''/Licence/Laguages/Language'') a(b)
end'

select * from Tbl

drop table Tbl

Результат этого точно такой же, как у marc_s в его примере, и это то, что я хотел.

Спасибо всем за ответы и помощь!