I have a table that contains a column that is XML data type. I am looking to put a view over the entire table including the XML data type column. The view will expand contents of the XML data type column. I am having a problem being able to traverse the entire XML document and pick up values from particular XML nodes.
I am using this SQL query and it's only picking up the first instance of value for the FuelPathwayCode XML node. I am looking to traverse an entire XML document and query ALL values of the /FuelPathwayCode XML node in one result set?
SELECT
UploadFileID, Year, Quarter,
FileContent.value('(LCFS-Report/Fuel/FuelPathwayCode)[1]', 'varchar(100)') as FuelPathwayCode
FROM LC_UploadXM
I am looking for a result set like this:
UploadFileID Year Quarter FuelPathWayCode PhysicalPathwayCode
8 2010 4 CARBOB001 PP001
8 2010 4 CARBOB002-HIGH PP001
Table Columns:
UploadFileID,
Year,
Quarter,
CompanyID,
F开发者_开发知识库ileType,
FileContent,
FileName,
Description,
Success,
AddBy,
AddDttm
You need to do a CROSS APPLY and pick the bit of the XML that you need, and then you need to do a series of .value
calls on these:
SELECT
UploadFileID, Year, Quarter,
FC.Node.value('(FuelPathwayCode)[1]', 'varchar(100)') as FuelPathwayCode
FROM
LC_UploadXM
CROSS APPLY
FileContent.nodes('/LCFS-Report/Fuel') as FC(Node)
This basically takes all your XML nodes that are present in that XPath /LCFS-Report/Fuel
and uses them as if they're rows in a table (schema is FC, "virtual table" name is "Node" - those can be used however you like, totally up to you), and then cross applies those rows to the base select.
That "virtual table" FC.Node can now be queried for individual values - e.g. your "pointer" in the XML is already sitting on the "Fuel" subnode in the XML, and now you can access the individual values inside it using the .value()
call.
Check out Alex Homer's SQL Server 2005 XQuery intro article - excellent resource!
Value is a scalar function. Try using query() instead.
http://msdn.microsoft.com/en-us/library/ms191474.aspx
精彩评论