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

I have a table on MS SQL server that holds information about reports in XML format. The table consists of two fields: the first has the business key, the second the entire report in XML format.

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

These reports include several pictures each. The XML holds information about these pictures, such as their filename, taken date, etc. I want to extract this information into a table, where every record holds information about exactly one photo. I've found ways to do this that come very close, but the problem I keep running into is that I need to create several records in this table for every record in my source table. How can I make this work?

这些报告中各有几张照片。XML保存关于这些图片的信息，比如它们的文件名、拍摄日期等。我已经找到了实现这一点的方法，但是我经常遇到的问题是，我需要在这个表中为源表中的每个记录创建几个记录。我怎样才能让它工作呢?

The business key needs to be in the final table as well. This business key can be found in the XML data, but there is also a separate field in the source table (as mentioned before) where it can be found. The content of the XML column could look similar to this:

业务键也需要在最终表中。这个业务键可以在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>

I want the final table to look like this:

我希望最后的表格是这样的:

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

3 个解决方案

#1

According to comments the OP needs an approach to get this from table row data and the existing answer is not solution enough.

根据注释，OP需要一种方法从表行数据中获取这些数据，现有的答案还不够解决方案。

You might try this:

您可以试一试这个:

CREATE TABLE #YourTable(BusinessKey VARCHAR(10),ReportData XML);
INSERT INTO #YourTable VALUES
 ('0000001','<Report>
              <ReportKey>0000001</ReportKey>
              <Photos>
                <Photo>
                  <Filename>1.jpg</Filename>
                  <Date>2015-01-01</Date>
                </Photo>
                <Photo>
                  <Filename>2.jpg</Filename>
                  <Date>2016-05-13</Date>
                </Photo>
              </Photos>
            </Report>')
,('0000002','<Report>
              <ReportKey>0000002</ReportKey>
              <Photos>
                <Photo>
                  <Filename>3.jpg</Filename>
                  <Date>2015-04-19</Date>
                </Photo>
                <Photo>
                  <Filename>4.jpg</Filename>
                  <Date>2016-12-10</Date>
                </Photo>
              </Photos>
            </Report>');

SELECT BusinessKey AS Table_Key 
      ,ReportData.value('(/Report/ReportKey)[1]','varchar(10)') AS XML_Key
      ,Photo.value('Filename[1]','varchar(max)') AS Photo_Filename
      ,Photo.value('Date[1]','date') AS Photo_Date
FROM #YourTable
CROSS APPLY ReportData.nodes('/Report/Photos/Photo') AS A(Photo);
GO

DROP TABLE #YourTable;

#2

This is not an answer, but important enough not to end up in a comment:

这不是一个答案，但重要的是不要以评论结束:

Be very careful with date formats. I do not know how your XML is generated, but the date within an XML should be ISO 8601 (yyyy-mm-dd or yyyy-mm-ddThh:mm:ss).

注意日期格式。我不知道如何生成XML，但是XML中的日期应该是ISO 8601 (yyyyy -mm-dd或yyyyy -mm- ddthh:mm:ss)。

Your format is culture dependant !!!

您的格式是文化依赖!!!

Try this:

试试这个:

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

You see, that the results differ?

你看，结果不同吗?

Now try to set the date to the 13th of March. There's even a conversion exception!

现在试着把日期定在3月13日。甚至还有一个转换异常!

#3

Maybe I misunderstood the question. However, try this.

也许我误解了这个问题。然而,试试这个。

create table t (
    [Key]      int,
    [Filename] nvarchar(max),
    [Date]     date
)

declare @xml as 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>'

insert into t ([Key], [Filename], [Date])
select n.value('ReportKey[1]', 'int')
     , x.value('Filename[1]',  'nvarchar(max)')
     , x.value('Date[1]',      'date')   
from @xml.nodes('Report') as r(n)
cross apply r.n.nodes('Photos/Photo') as t(x)

select * from t

#1