Блог

Каков был бы правильный способ разбора следующего 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);
  

Вы можете использовать таблицу 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'))
  

Используйте функцию OPENXML. Это поставщик набора строк (он возвращает набор строк, проанализированных из XML) и, таким образом, может использоваться в SELECT или INSERT like:

 INSERT INTO table SELECT * FROM OPENXML(source, rowpattern, flags)
  

Пожалуйста, посмотрите первый пример по ссылке на документацию для наглядности.

Обычно, если вы хотите проанализировать XML, вы бы сделали это на языке программирования, таком как Perl, Python, Java или C #, который а) имеет XML DOM и б) может взаимодействовать с реляционной базой данных.

Вот короткая статья, которая показывает вам некоторые основы чтения и записи XML на C # … и даже содержит пример того, как создать XML-документ из SQL-запроса (в одной строке!).:

http://www.c-sharpcorner.com/uploadfile/mahesh/readwritexmltutmellli2111282005041517am/readwritexmltutmellli21.aspx