How to extract data from xml column in sql 2008
I have a table with a column of type xml. I need to extract values ββfrom xml column. I tried every possible way and I could not succeed. Please find below sample 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.
Thanks, Darthick. s.darthick@gmail.com
+1
source to share
2 answers
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)
Result:
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
+2
source to share
If namespace exists. XML:
<PersonInfo xmlns="http://mynamspace.lv/default">
<Person ID="22">
<Name>MyName</Name>
<Profession>MyProfession</Profession>
</Person>
</PersonInfo>
Then you should do something like this:
WITH XMLNAMESPACES (DEFAULT 'http://mynamspace.lv/default')
SELECT
@myXML.value('(PersonInfo/Person/@ID)[1]', 'nvarchar(100)') as ID,
@myXML.value('(PersonInfo/Person/Name)[1]', 'nvarchar(100)') as Name,
@myXML.value('(PersonInfo/Person/Profession)[1]', 'nvarchar(100)') as Profession;
+1
source to share