选择XML节点作为行

我从具有使用T-SQL的XML列的表中选择。我想选择某种类型的节点,并为每个节点创建一行。

例如,假设我要从 人员 表中进行选择。该表具有用于 地址 的XML列。XML的格式类似于以下格式:

<address>

<street>Street 1</street>

<city>City 1</city>

<state>State 1</state>

<zipcode>Zip Code 1</zipcode>

</address>

<address>

<street>Street 2</street>

<city>City 2</city>

<state>State 2</state>

<zipcode>Zip Code 2</zipcode>

</address>

我如何获得这样的结果:

听听听听听听听听听 听听听听听听听听听

乔·贝克|西雅图WA

乔·贝克|塔科马| WA

弗雷德·琼斯温哥华

回答:

这是您的解决方案:

/* TEST TABLE */

DECLARE @PEOPLE AS TABLE ([Name] VARCHAR(20), [Address] XML )

INSERT INTO @PEOPLE SELECT

'Joel',

'<address>

<street>Street 1</street>

<city>City 1</city>

<state>State 1</state>

<zipcode>Zip Code 1</zipcode>

</address>

<address>

<street>Street 2</street>

<city>City 2</city>

<state>State 2</state>

<zipcode>Zip Code 2</zipcode>

</address>'

UNION ALL SELECT

'Kim',

'<address>

<street>Street 3</street>

<city>City 3</city>

<state>State 3</state>

<zipcode>Zip Code 3</zipcode>

</address>'

SELECT * FROM @PEOPLE

-- BUILD XML

DECLARE @x XML

SELECT @x =

( SELECT

[Name]

, [Address].query('

for $a in //address

return <address

street="{$a/street}"

city="{$a/city}"

state="{$a/state}"

zipcode="{$a/zipcode}"

/>

')

FROM @PEOPLE AS people

FOR XML AUTO

)

-- RESULTS

SELECT [Name] = T.Item.value('../@Name', 'varchar(20)'),

street = T.Item.value('@street' , 'varchar(20)'),

city = T.Item.value('@city' , 'varchar(20)'),

state = T.Item.value('@state' , 'varchar(20)'),

zipcode = T.Item.value('@zipcode', 'varchar(20)')

FROM @x.nodes('//people/address') AS T(Item)

/* OUTPUT*/

Name | street | city | state | zipcode

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Joel | Street 1 | City 1 | State 1 | Zip Code 1

Joel | Street 2 | City 2 | State 2 | Zip Code 2

Kim | Street 3 | City 3 | State 3 | Zip Code 3

以上是 选择XML节点作为行 的全部内容, 来源链接: utcz.com/qa/431963.html

回到顶部