我在ms sql server上有一个表,它保存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>

我希望最后一张桌子是这样的:
+---------+----------+------------+
|   Key   | Filename |    Date    |
+---------+----------+------------+
| 0000001 | 1.jpg    | 01-01-2015 |
| 0000001 | 2.jpg    | 01-01-2016 |
+---------+----------+------------+

最佳答案

根据注释,OP需要一种从表行数据中获取此信息的方法,而现有的答案还不够解决。
你可以试试这个:

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;

09-30 15:15
查看更多