我从一个使用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
弗雷德·琼斯温哥华BC
解决方法
这是您的解决方案：
/* 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',city = T.Item.value('@city',state = T.Item.value('@state',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
