从 SQL Server 2008 中的 XML 字段中选择值 [英] Select values from XML field in SQL Server 2008

问题描述

看看我的 XML 字段，我的行看起来像这样:

Just looking at my XML field, my rows look like this:

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

请注意，这是我表格中的三行.

Note that these are three rows in my table.

我想将 SQL 结果作为表返回

I'd like to return a SQL result as a table as in

Jon  | Johnson
Kathy| Carter
Bob  | Burns

什么查询会完成这个?

推荐答案

鉴于 XML 字段名为 'xmlField'...

Given that the XML field is named 'xmlField'...

SELECT 
[xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName,
[xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName
FROM [myTable]

这篇关于从 SQL Server 2008 中的 XML 字段中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！