我在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;