将嵌套的 XML 解析为 SQL 表 [英] Parsing nested XML into SQL table

问题描述

根据所需的布局(如下)将以下 XML 块解析为 SQL Server 表的正确方法是什么?是否可以使用单个 SELECT 语句来完成，而无需 UNION 或循环?有接班人吗?提前致谢.输入 XML:

What would be the right way to parse the following XML block into SQL Server table according to desired layout (below)? Is it possible to do it with a single SELECT statement, without UNION or a loop? Any takers? Thanks in advance. Input 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

我正在使用 OPENXML 或 xml.nodes() 功能执行 SELECT SQL 语句.例如，以下 SELECT 语句导致值和日期之间的产生(即值和日期的所有排列)，这是我想要避免的.

I am after an SELECT SQL statement using OPENXML or xml.nodes() functionality. For example, the following SELECT statement results in production between Values and Dates (that is all permutations of Values and Dates), which is something I want to avoid.

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);

推荐答案

您可以使用数字表从子元素中选择第一、第二、第三等行.在此查询中，如果提供日期，我将返回的行数限制为数字.如果值或描述多于日期，您必须修改联接以将其考虑在内.

You can make use of a numbers table to pick the first, second, third etc row from the child elements. In this query I have limited the rows returned to the number if dates provided. If there are more values or descriptions than dates you have to modify the join to take that into account.

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'))

这篇关于将嵌套的 XML 解析为 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！