CODEWITHSUNDEEP
sqlxmlsql-server-2008view
Lakeshore
Lakeshore

Reputation: 323

A View Over a XML Data Type Column

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

Upvotes: 0

Views: 839

Answers (2)

boydc7
boydc7

Reputation: 4633

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.

Upvotes: 0

Swati
Swati

Reputation: 52817

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.

Upvotes: 4

Related Questions