从SQL Server 2008中的XML字段中选择值

Question

仅查看我的XML字段，我的行如下所示：

<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>

<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>

<person><firstName>Bob</firstName><lastName>Burns</lastName></person>

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

我想以表格形式返回SQL结果

Jon  | Johnson

Kathy| Carter

Bob  | Burns

什么查询将完成此任务？

Answer 1

考虑到XML数据来自表'table'并存储在'field'列中：使用XML方法，使用提取值，使用提取xml.value()项目节点xml.nodes()，使用CROSS APPLY联接：SELECT&nbsp;&nbsp; &nbsp; p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,&nbsp; &nbsp; p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastNameFROM table&nbsp;&nbsp; &nbsp; CROSS APPLY field.nodes('/person') t(p)如果每个字段仅包含一个元素“ person” nodes()，cross apply则可以放弃。如果XML是变量，则选择FROM @variable.nodes(...)，则不需要cross apply。

Answer 2

这篇文章对解决我的XML格式略有不同的问题很有帮助...我的XML包含一个键列表，例如以下示例，我将XML存储在名为DeleteBatch的表的SourceKeys列中：<k>1</k><k>2</k><k>3</k>创建表并用一些数据填充它：CREATE TABLE dbo.DeleteBatch (&nbsp; &nbsp; ExecutionKey INT PRIMARY KEY,&nbsp; &nbsp; SourceKeys XML)INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )SELECT 1,&nbsp;&nbsp; &nbsp; (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )SELECT 2,&nbsp;&nbsp; &nbsp; (CAST('<k>100</k><k>101</k>' AS XML))这是从XML中选择键的SQL：SELECT ExecutionKey, p.value('.', 'int') AS [Key]FROM dbo.DeleteBatch&nbsp; &nbsp; CROSS APPLY SourceKeys.nodes('/k') t(p)这是查询结果...ExecutionKey键1 11 21 32 1002 101

Answer 3

这可能会回答您的问题：select cast(xmlField as xml) xmlField into tmp from (select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlFieldunion select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>') tbSELECT&nbsp; &nbsp; xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName&nbsp; &nbsp; ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastNameFROM tmpdrop table tmp