#sql #sql-server #xml #xml-parsing
#sql #sql-сервер #xml #xml-синтаксический анализ
Вопрос:
Каков был бы правильный способ разбора следующего XML-блока в таблицу SQL Server в соответствии с желаемым макетом (ниже)? Возможно ли это сделать с помощью одной инструкции SELECT, без ОБЪЕДИНЕНИЯ или цикла? Есть желающие? Заранее спасибо. Ввод XML:
<ObjectData>
<Parameter1>some value</Parameter1>
<Parameter2>other value</Parameter2>
<Dates>
<dateTime>2011-02-01T00:00:00</dateTime>
<dateTime>2011-03-01T00:00:00</dateTime>
<dateTime>2011-04-01T00:00:00</dateTime>
</Dates>
<Values>
<double>0.019974</double>
<double>0.005395</double>
<double>0.004854</double>
</Values>
<Description>
<string>this is row 1</string>
<string>this is row 2</string>
<string>this is row 3</string>
</Values>
</ObjectData>
Желаемый вывод таблицы:
Parameter1 Parameter2 Dates Values Description
Some value Other value 2011-02-01 00:00:00.0 0.019974 this is row 1
Some value Other value 2011-03-01 00:00:00.0 0.005395 this is row 2
Some value Other value 2011-04-01 00:00:00.0 0.004854 this is row 3
Я ищу инструкцию SELECT SQL, используя функциональность OPENXML или xml.nodes(). Например, следующий оператор SELECT приводит к созданию между значениями и датами (то есть ко всем перестановкам значений и дат), чего я хочу избежать.
SELECT
doc.col.value('Parameter1[1]', 'varchar(20)') Parameter1,
doc.col.value('Parameter2[1]', 'varchar(20)') Parameter2,
doc1.col.value('.', 'datetime') Dates ,
doc2.col.value('.', 'float') [Values]
FROM
@xml.nodes('/ObjectData') doc(col),
@xml.nodes('/ObjectData/Dates/dateTime') doc1(col),
@xml.nodes('/ObjectData/Values/double') doc2(col);
Ответ №1:
Вы можете использовать таблицу numbers для выбора первой, второй, третьей и т.д. Строки из дочерних элементов. В этом запросе я ограничил возвращаемые строки числом, если указаны даты. Если значений или описаний больше, чем дат, вам придется изменить объединение, чтобы учесть это.
declare @XML xml = '
<ObjectData>
<Parameter1>some value</Parameter1>
<Parameter2>other value</Parameter2>
<Dates>
<dateTime>2011-02-01T00:00:00</dateTime>
<dateTime>2011-03-01T00:00:00</dateTime>
<dateTime>2011-04-01T00:00:00</dateTime>
</Dates>
<Values>
<double>0.019974</double>
<double>0.005395</double>
<double>0.004854</double>
</Values>
<Description>
<string>this is row 1</string>
<string>this is row 2</string>
<string>this is row 3</string>
</Description>
</ObjectData>'
;with Numbers as
(
select number
from master..spt_values
where type = 'P'
)
select T.N.value('Parameter1[1]', 'varchar(50)') as Parameter1,
T.N.value('Parameter2[1]', 'varchar(50)') as Parameter2,
T.N.value('(Dates/dateTime[position()=sql:column("N.Number")])[1]', 'datetime') as Dates,
T.N.value('(Values/double[position()=sql:column("N.Number")])[1]', 'float') as [Values],
T.N.value('(Description/string[position()=sql:column("N.Number")])[1]', 'varchar(max)') as [Description]
from @XML.nodes('/ObjectData') as T(N)
cross join Numbers as N
where N.number between 1 and (T.N.value('count(Dates/dateTime)', 'int'))
Комментарии:
1. Большое тебе спасибо, Микаэль. Это действительно работает хорошо! Синтаксис по-прежнему ошеломляющий, я представлял его немного проще … 🙂
Ответ №2:
Используйте функцию OPENXML. Это поставщик набора строк (он возвращает набор строк, проанализированных из XML) и, таким образом, может использоваться в SELECT или INSERT like:
INSERT INTO table SELECT * FROM OPENXML(source, rowpattern, flags)
Пожалуйста, посмотрите первый пример по ссылке на документацию для наглядности.
Ответ №3:
Обычно, если вы хотите проанализировать XML, вы бы сделали это на языке программирования, таком как Perl, Python, Java или C #, который а) имеет XML DOM и б) может взаимодействовать с реляционной базой данных.
Вот короткая статья, которая показывает вам некоторые основы чтения и записи XML на C # … и даже содержит пример того, как создать XML-документ из SQL-запроса (в одной строке!).:
Комментарии:
1. Спасибо, но это не то, что я ищу. Я ищу инструкцию SQL, используя OPENXML или функциональность xml.nodes (). Следующий оператор select приводит к созданию между значениями и датами, чего я хочу избежать. ВЫБЕРИТЕ doc.col.value(‘Parameter1[1]’, ‘varchar(20)’) Параметр1, документ со значением (‘Parameter2[1]’, ‘varchar(20)’) Параметр2, doc1.col.value(‘.’, ‘datetime’) Даты,doc2.col.value(‘.[1]’, ‘float’) [Значения] ИЗ @xml.nodes(‘/ObjectData’) doc (col),@xml.nodes (‘/ObjectData/Даты /DateTime’) doc1 (col), @xml.nodes (‘/ObjectData/Values/double’) doc2 (col);