从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  | JohnsonKathy| CarterBob  | Burns什么查询将完成此任务？

Answer 1

考虑到XML数据来自表'table'并存储在'field'列中：使用XML方法，使用提取值，使用提取xml.value()项目节点xml.nodes()，使用CROSS APPLY联接：

SELECT 

    p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName,

    p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName

FROM table 

    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 (

    ExecutionKey INT PRIMARY KEY,

    SourceKeys XML)

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )

SELECT 1, 

    (CAST('<k>1</k><k>2</k><k>3</k>' AS XML))

INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )

SELECT 2, 

    (CAST('<k>100</k><k>101</k>' AS XML))

这是从XML中选择键的SQL：

SELECT ExecutionKey, p.value('.', 'int') AS [Key]

FROM dbo.DeleteBatch

    CROSS APPLY SourceKeys.nodes('/k') t(p)

这是查询结果...

ExecutionKey键

1 1

1 2

1 3

2 100

2 101

Answer 3

这可能会回答您的问题：

select cast(xmlField as xml) xmlField into tmp from (

select '<person><firstName>Jon</firstName><lastName>Johnson</lastName></person>' xmlField

union select '<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>'

union select '<person><firstName>Bob</firstName><lastName>Burns</lastName></person>'

) tb

SELECT

    xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName

    ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName

FROM tmp

drop table tmp