How to select data from xml data

mpolaiah · May 18, 2018, 1:33pm

Hi all,

m‌y front end appliction store the data in xml data type but we select the data in query.
m‌y xml data like
‌
‌

F‌inaly we select the data like

s‌eelct nodename(block/item),url,type‌‌‌‌,title

P‌lease help....asap‌

yosiasz · May 18, 2018, 2:17pm

provide xml ...asap

yosiasz · May 18, 2018, 2:24pm

that is a very blank xml

yosiasz · May 18, 2018, 2:29pm

Copy the xml to notepad then copy it from notepad to this web site.

mpolaiah · May 18, 2018, 2:37pm

i am doing same thing but not post in website

yosiasz · May 18, 2018, 2:48pm

post a screen shot of it

mpolaiah · May 18, 2018, 2:59pm

yosiasz · May 18, 2018, 3:18pm

and now please post the final result you want queried out of this xml?

eclare @fakenews as xml = 
'<searchResult>
	<section col="main">
		<block type="topStories" url="">
			<item url="https://en.m.wikipedia.org/wiki/Donal_trump" />
		</block>
		<item url="https://en.m.wikipedia.org/wiki/Donal_trump" />
	</section>
	<section col="right" />
</searchResult>'

select x.v.value('item[1]/@url', 'varchar(150)') as deep_state
from @fakenews.nodes('/searchResult/section/block') x(v)

mpolaiah · May 18, 2018, 3:25pm

seelct nodename(block/item),url,type,title

yosiasz · May 18, 2018, 3:31pm

which url do you want? url from or url from item because I see two item nodes
one under block and one under section?

yosiasz · May 18, 2018, 3:36pm

declare @fakenews as xml = 
'<searchResult>
	<section col="main">
		<block type="topStories" url="">
			<item url="https://en.m.wikipedia.org/wiki/Saadam_Hussein" />
		</block>
		<block type="knowledgeGraph" url="" />
		<block type="relatedSearches" url="">
			<item url="" title="donal trump twitter" />
		</block>
		<item url="https://en.m.wikipedia.org/wiki/Donal_trump" />
	</section>
	<section col="right" />
</searchResult>'

select x.y.value('../item[1]/@url', 'varchar(150)') as section_item_url,  
	   x.y.value('@type', 'varchar(150)') as section_block_type,
	   x.y.value('item[1]/@url', 'varchar(150)') as section_block_item_url

  from @fakenews.nodes('/searchResult/section/block') x(y)

mpolaiah · May 18, 2018, 4:18pm

my xmldata like

mpolaiah · May 18, 2018, 4:22pm

but result selection like aaaa

here i tam urls and title are missing....please check once

mpolaiah · May 18, 2018, 4:23pm

and also each block is multiple urls