在SQL Server 2008中从XML字段中选择值。

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

看看我的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>

Note that these are three rows in my table.

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

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

我想返回一个SQL结果作为表。

Jon  | Johnson
Kathy| Carter
Bob  | Burns

What query will accomplish this?

什么查询可以完成这个任务?

8 个解决方案

#1

135

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

考虑到XML字段被命名为“xmlField”…

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

#2

104

Considering that XML data comes from a table 'table' and is stored in a column 'field': use the XML methods, extract values with xml.value(), project nodes with xml.nodes(), use CROSS APPLY to join:

考虑到XML数据来自表“表”，并存储在列“字段”中:使用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)

You can ditch the nodes() and cross apply if each field contains exactly one element 'person'. If the XML is a variable you select FROM @variable.nodes(...) and you don't need the cross apply.

如果每个字段只包含一个元素“person”，则可以抛弃节点()和交叉应用程序。如果XML是一个变量，您可以从@variable.nodes(…)中选择，而您不需要交叉应用。

#3

This post was helpful to solve my problem which has a little different XML format... my XML contains a list of keys like the following example and I store the XML in the SourceKeys column in a table named DeleteBatch:

这篇文章有助于解决我的问题，它有一些不同的XML格式……我的XML包含一个键列表，比如下面的例子，我将XML存储在一个名为DeleteBatch的表中，它位于SourceKeys列中:

<k>1</k>
<k>2</k>
<k>3</k>

Create the table and populate it with some data:

创建表并使用一些数据填充它:

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))

Here's my SQL to select the keys from the XML:

这里是我从XML中选择键的SQL:

SELECT ExecutionKey, p.value('.', 'int') AS [Key]
FROM dbo.DeleteBatch
    CROSS APPLY SourceKeys.nodes('/k') t(p)

Here's the query results...

这是查询结果……

ExecutionKey    Key
1   1
1   2
1   3
2   100
2   101

#4

This may answer your question:

这可以回答你的问题:

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

#5

Blimey. This was a really useful thread to discover.

啊呀。这是一个非常有用的线索。

I still found some of these suggestions confusing. Whenever I used value with [1] in the string, it would only retrieved the first value. And some suggestions recommended using cross apply which (in my tests) just brought back far too much data.

我仍然觉得这些建议有些令人困惑。每当我在字符串中使用[1]值时，它只会检索到第一个值。还有一些建议建议使用cross apply(在我的测试中)，它只是带来了太多的数据。

So, here's my simple example of how you'd create an xml object, then read out its values into a table.

下面是我的简单示例，说明如何创建xml对象，然后将其值读入表。

DECLARE @str nvarchar(2000)

SET @str = ''
SET @str = @str + '<users>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mike</firstName>'
SET @str = @str + '     <lastName>Gledhill</lastName>'
SET @str = @str + '     <age>31</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Mark</firstName>'
SET @str = @str + '     <lastName>Stevens</lastName>'
SET @str = @str + '     <age>42</age>'
SET @str = @str + '  </user>'
SET @str = @str + '  <user>'
SET @str = @str + '     <firstName>Sarah</firstName>'
SET @str = @str + '     <lastName>Brown</lastName>'
SET @str = @str + '     <age>23</age>'
SET @str = @str + '  </user>'
SET @str = @str + '</users>'

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 

--  Iterate through each of the "users\user" records in our XML
SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName',
    x.Rec.query('./age').value('.', 'int') AS 'Age'
FROM @xml.nodes('/users/user') as x(Rec)

And here's the output:

这是输出:

It's bizarre syntax, but with a decent example, it's easy enough to add to your own SQL Server functions.

这是一种奇怪的语法，但是有一个很好的例子，它很容易添加到您自己的SQL Server函数中。

Speaking of which, here's the correct answer to this question.

说到这里，这是对这个问题的正确答案。

Assuming your have your xml data in an @xml variable of type xml (as demonstrated in my example above), here's how you would return the three rows of data from the xml quoted in the question:

假设您的xml数据位于xml类型的@xml变量中(如上例所示)，下面是如何从问题中引用的xml中返回三行数据:

SELECT 
    x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName',
    x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName'
FROM @xml.nodes('/person') as x(Rec)

#6

If you are able to wrap your XML in a root element - say then the following is your solution:

如果您能够将XML封装到根元素中——那么下面是您的解决方案:

DECLARE @PersonsXml XML = '<persons><person><firstName>Jon</firstName><lastName>Johnson</lastName></person>
<person><firstName>Kathy</firstName><lastName>Carter</lastName></person>
<person><firstName>Bob</firstName><lastName>Burns</lastName></person></persons>'

SELECT  b.value('(./firstName/text())[1]','nvarchar(max)') as FirstName, b.value('(./lastName/text())[1]','nvarchar(max)') as LastName
FROM @PersonsXml.nodes('/persons/person') AS a(b)

#7

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

#8

/* This example uses an XML variable with a schema */

/*这个例子使用一个带有模式*/的XML变量。

IF EXISTS (SELECT * FROM sys.xml_schema_collections 
           WHERE name = 'OrderingAfternoonTea')
BEGIN
    DROP XML SCHEMA COLLECTION dbo.OrderingAfternoonTea 
END
GO

CREATE XML SCHEMA COLLECTION dbo.OrderingAfternoonTea AS
N'<?xml version="1.0" encoding="UTF-16" ?>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
     targetNamespace="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     xmlns="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea"
     elementFormDefault="qualified"
     version="0.10"
   > 
    <xsd:complexType name="AfternoonTeaOrderType">
       <xsd:sequence>
         <xsd:element name="potsOfTea" type="xsd:int"/>
         <xsd:element name="cakes" type="xsd:int"/>
         <xsd:element name="fruitedSconesWithCream" type="xsd:int"/>
         <xsd:element name="jams" type="xsd:string"/>
      </xsd:sequence>
      <xsd:attribute name="schemaVersion" type="xsd:long" use="required"/>
    </xsd:complexType>

    <xsd:element name="afternoonTeaOrder"
                 type="TFor2:AfternoonTeaOrderType"/>

  </xsd:schema>' ;
GO

DECLARE @potsOfTea int;
DECLARE @cakes int;
DECLARE @fruitedSconesWithCream int;
DECLARE @jams nvarchar(128);

DECLARE @RequestMsg NVARCHAR(2048);
DECLARE @RequestXml XML(dbo.OrderingAfternoonTea);

set @potsOfTea = 5;
set @cakes = 7;
set @fruitedSconesWithCream = 25;
set @jams = N'medlar jelly, quince and mulberry';

SELECT @RequestMsg = N'<?xml version="1.0" encoding="utf-16" ?>
<TFor2:afternoonTeaOrder schemaVersion="10"
    xmlns:TFor2="http://Tfor2.com/schemas/actions/orderAfternoonTea">
    <TFor2:potsOfTea>' + CAST(@potsOfTea as NVARCHAR(20)) 
        + '</TFor2:potsOfTea>
    <TFor2:cakes>' + CAST(@cakes as NVARCHAR(20)) + '</TFor2:cakes>
    <TFor2:fruitedSconesWithCream>' 
        + CAST(@fruitedSconesWithCream as NVARCHAR(20))
        + '</TFor2:fruitedSconesWithCream>
    <TFor2:jams>' + @jams + '</TFor2:jams>
</TFor2:afternoonTeaOrder>';

SELECT @RequestXml  = CAST(CAST(@RequestMsg AS VARBINARY(MAX)) AS XML) ;

with xmlnamespaces('http://Tfor2.com/schemas/actions/orderAfternoonTea'
                    as tea)
select
    cast( x.Rec.value('.[1]/@schemaVersion','nvarchar(20)') as bigint )
        as schemaVersion,
    cast( x.Rec.query('./tea:potsOfTea')
               .value('.','nvarchar(20)') as bigint ) as potsOfTea,
    cast( x.Rec.query('./tea:cakes')
               .value('.','nvarchar(20)') as bigint )  as cakes,
    cast( x.Rec.query('./tea:fruitedSconesWithCream')
               .value('.','nvarchar(20)') as bigint ) 
      as fruitedSconesWithCream,
    x.Rec.query('./tea:jams').value('.','nvarchar(50)')  as jams
from @RequestXml.nodes('/tea:afternoonTeaOrder')  as x(Rec);

select @RequestXml.query('/*')

#1

135