A View Over a XML Data Type Column

Question

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.

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

Answer 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.

This is supported in SQL Server 2008.

Answer 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.