XML数据类型列上的视图

I have a table that contains a column of XML Datatype (column name FileContent). I want to create a view that queries the contents of the XML datatype column so that I no longer have FileContent column but two additional columns called FuelPathwayCode and PhysicalPathwayCode from the underlying XML document. Is this possible with SQL Server 2008? And, if so, how? Thanks.

我有一个包含XML数据类型(列名FileContent)列的表。我想创建一个视图，查询XML数据类型列的内容，这样我就不再有FileContent列，而是从底层XML文档中添加两个名为FuelPathwayCode和PhysicalPathwayCode的列。SQL Server 2008能做到吗?,如果是,如何?谢谢。

CREATE VIEW vw_LC_UploadXML
AS Select
  UploadFileID,
  Year,
  Quarter,
  CompanyID,
  FileType,
  FileContent,
  FileName,
  Description,
  Success,
  AddBy,
  AddDttm
FROM LC_UploadXML

2 个解决方案

#1

http://msdn.microsoft.com/en-us/library/bb500166.aspx

Use FileContent.value('(/FuelPathwayCode/@year)[1]', 'int(4)') to retrieve the particular field you are looking for.

使用FileContent.value('(/FuelPathwayCode/@year)[1]'、'int(4)')检索您要查找的特定字段。

This is supported in SQL Server 2008.

这在SQL Server 2008中得到支持。

#2

XML has fairly rich support in Sql Server 2k5 and up, a variety of options here. You can try the xml data type methods for querying, pulling values from, iterating over nodes in the document, and modifying the content. This is probably the best option for use in a view, in your case you'll likely want to check out the value method.

XML在Sql Server 2k5和up中有相当丰富的支持，这里有各种各样的选项。您可以尝试使用xml数据类型方法查询、提取值、遍历文档中的节点并修改内容。这可能是在视图中使用的最佳选项，在您的情况下，您可能希望检查value方法。

#1