如何从 sql 2008 中的 xml 列中提取数据 [英] How to Extract data from xml column in sql 2008
本文介绍了如何从 sql 2008 中的 xml 列中提取数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含 xml 类型列的表.我必须从 xml 列中提取值.我已经尝试了所有可能的方法,但我没有成功.请在下面的 xml 数据示例中找到.
I have a table with a column of type xml. I have to extract the values from the xml column. I have tried all possible ways and i couldn succeed.Please find below the sample of the xml data.
<Menu>
<Id>1</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg</Url>
</Menu>
<Menu>
<Id>2</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg</Url>
</Menu>
<Menu>
<Id>3</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg</Url>
</Menu>
<Menu>
<Id>4</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg</Url>
</Menu>
<Menu>
<Id>5</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg</Url>
</Menu>
请帮我提取数据.提前致谢.
Please help me to extract the data. Tons of thanks in advance.
谢谢,达西克.s.darthick@gmail.com
Thanks, Darthick. s.darthick@gmail.com
推荐答案
declare @T table
(
XMLCol xml
)
insert into @T values
('<Menu>
<Id>1</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg</Url>
</Menu>
<Menu>
<Id>2</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg</Url>
</Menu>
<Menu>
<Id>3</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg</Url>
</Menu>
<Menu>
<Id>4</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg</Url>
</Menu>
<Menu>
<Id>5</Id>
<Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg</Url>
</Menu>')
select X.N.value('Id[1]', 'int') as Id,
X.N.value('Url[1]', 'varchar(max)') as Url
from @T as T
cross apply T.XMLCol.nodes('/Menu') as X(N)
结果:
Id Url
----------- ---------------------------------------------------------------------
1 http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
2 http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg
3 http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg
4 http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg
5 http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg
这篇关于如何从 sql 2008 中的 xml 列中提取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文