sql-server - 将 1 对 n XML 列转换为表格数据

Question

我在 MS SQL 服务器上有一个表，其中包含有关 XML 格式的报告的信息。该表由两个字段组成:第一个字段包含业务键，第二个字段是 XML 格式的整个报告。

这些报告各包含几张图片。 XML 包含有关这些图片的信息，例如它们的文件名、拍摄日期等。我想将这些信息提取到一个表中，其中每条记录只包含一张照片的信息。我已经找到了非常接近的方法来做到这一点，但我一直遇到的问题是我需要在这个表中为源表中的每条记录创建几条记录。我怎样才能使它工作？

业务键也需要在最终表中。这个业务键可以在 XML 数据中找到，但在源表(如前所述)中也有一个单独的字段可以找到它。 XML 列的内容可能与此类似:

<Report>
  <ReportKey>0000001</ReportKey>
  [...]
  <Photos>
    <Photo>
      <Filename>1.jpg</Filename>
      <Date>01-01-2015</Date>
    </Photo>
    <Photo>
      <Filename>2.jpg</Filename>
      <Date>01-01-2016</Date>
    </Photo>
    [...]
  </Photos>
  [...]
</Report>

我希望决赛 table 看起来像这样:

+---------+----------+------------+
|   Key   | Filename |    Date    |
+---------+----------+------------+
| 0000001 | 1.jpg    | 01-01-2015 |
| 0000001 | 2.jpg    | 01-01-2016 |
+---------+----------+------------+

Answer 1

这不是一个答案，但足够重要，不会在评论中结束:

要非常小心日期格式。我不知道您的 XML 是如何生成的，但是 XML 中的日期 within 应该是 ISO 8601(yyyy-mm-dd 或 yyyy-mm-ddThh: mm:ss).

您的格式文化依赖!!!

试试这个:

set language french;
declare @xml as xml ='<x><Date>08-03-2015</Date></x>';
select @xml.value('(/x/Date)[1]','datetime');

set language english;
select @xml.value('(/x/Date)[1]','datetime');

你看，结果不一样？

现在尝试将日期设置为 3 月 13 日。甚至还有转换异常!