May 18, 2018 at 7:31 am
Hi all,
my front end appliction store the data in xml data type but we select the data in query.
my xml data like
<searchResult searchEngine="106" keyword="donald Trump" date="2018-05-18T09:00:34Z">
<section col="main">
<block type="topStories" url="">
<item url="https://www.theguardian.com/us-news/2018/may/18/bill-gates-donald-trump-difference-hiv-hpv" title="The Guardian ?? 5 hours ago" />
<item url="https://www.theguardian.com/us-news/2018/may/17/north-korea-trump-latest-warning-kim-jong-un-gaddafi" title="The Guardian ?? 13 hours ago" />
<item url="https://www.cnn.com/2018/05/17/politics/trump-planned-parenthood-funds/index.html" title="CNN.com" />
<item url="https://m.huffingtonpost.co.uk/entry/bil-gates-donald-trump_uk_5afe6f52e4b0a046186a09ad" title="HuffPost UK" />
</block>
<block type="knowledgeGraph" url="" />
<block type="videos" url="">
<item url="https://m.youtube.com/watch?v=ceNlAXhEfcE" title="Donald Trump Mocks Robert Mueller Investigation With ..." />
<item url="https://m.youtube.com/watch?v=QNLwyVxleQo" title="Donald Trump Remembered His Anniversary" />
</block>
<block type="twitterCards" url="https://twitter.com/realDonaldTrump?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Eauthor">
<item url="https://twitter.com/realDonaldTrump/status/997239623268085761?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997238932311068674?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997233764689498112?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997227223638790144?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997220260259487744?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
</block>
<item url="https://en.m.wikipedia.org/wiki/Donald_Trump" />
<item url="http://www.donaldjtrump.com/" />
<item url="https://www.telegraph.co.uk/donald-trump/" />
<item url="https://www.theatlantic.com/magazine/archive/2017/10/will-donald-trump-destroy-the-presidency/537921/" />
<item url="http://time.com/donald-trump-after-hours/" />
<item url="http://mobile.nytimes.com/2017/12/09/us/politics/donald-trump-president.html" />
<item url="http://thehill.com/people/donald-trump" />
<item url="https://www.whitehouse.gov/people/donald-j-trump/" />
<item url="https://www.mirror.co.uk/all-about/donald-trump" />
<item url="https://www.standard.co.uk/topic/donald-trump" />
<item url="https://m.facebook.com/DonaldTrump/" />
<item url="https://www.politico.com/news/donald-trump" />
<item url="https://www.express.co.uk/latest/donald-trump" />
<item url="https://www.thesun.co.uk/who/donald-trump/" />
<item url="http://time.com/donald-trump-after-hours/" />
<item url="https://www.ft.com/donald-trump" />
<item url="https://www.theatlantic.com/magazine/archive/2017/10/will-donald-trump-destroy-the-presidency/537921/" />
<item url="https://www.cnet.com/news/donald-trump-weighs-in-laurel-or-yanny-white-house/" />
<item url="https://www.biography.com/people/donald-trump-9511238" />
<item url="https://m.huffingtonpost.co.uk/news/donald-trump/" />
<item url="http://www.nytimes.com/topic/person/donald-trump" />
<item url="http://mobile.nytimes.com/2017/12/09/us/politics/donald-trump-president.html" />
<item url="https://abcnews.go.com/topics/news/donald-trump.htm" />
<item url="https://www.aljazeera.com/topics/people/donald-trump.html" />
<item url="http://www.dailymail.co.uk/news/donald_trump/index.html" />
<item url="http://people.com/tag/donald-trump/" />
<item url="https://www.usatoday.com/story/opinion/2018/05/16/donald-trump-supporters-across-america-hes-delivering-his-agenda/606334002/" />
<item url="https://www.thetimes.co.uk/topic/donald-trump" />
<item url="https://www.forbes.com/profile/donald-trump/" />
<item url="http://www.politifact.com/personalities/donald-trump/" />
<item url="https://www.trump.com/" />
<item url="https://www.cbsnews.com/news/senate-judiciary-releases-trump-tower-meeting-hearing-transcripts-live-updates/" />
<item url="http://mobile.abc.net.au/news/2018-05-18/donald-trump-latest-on-the-russia-investigation-one-year-in/9774822" />
<item url="http://www.msnbc.com/topics/donald-trump" />
<item url="http://www.latimes.com/topic/politics-government/donald-trump-PEBSL000163-topic.html" />
<item url="http://www.bbc.co.uk/news/topics/cp7r8vgl2lgt/donald-trump" />
<item url="https://globalnews.ca/tag/donald-trump/" />
<item url="https://www.vox.com/2018/5/16/17362870/trump-immigrants-animals-ms-13-illegal" />
<item url="https://www.washingtonpost.com/news/the-fix/wp/2018/05/16/54-things-donald-trump-jr-couldnt-recall-or-remember-in-his-testimony/?utm_term=.70231c4e9af8" />
<item url="https://www.bloomberg.com/view/articles/2018-05-17/sex-isn-t-everything-in-trump-s-big-reveal-timothy-l-o-brien" />
<item url="https://www.rollingstone.com/topic/donald-trump" />
<item url="http://www.businessinsider.com/bill-gates-donald-trump-hiv-hpv-2018-5" />
<item url="http://www.chicagotribune.com/topic/politics-government/donald-trump-PEBSL000163-topic.html" />
<item url="https://www.haaretz.com/whdcMobileSite/middle-east-news/.premium-the-day-donald-trump-was-crowned-king-in-jerusalem-1.6092216" />
<item url="https://www.nbcnews.com/politics/donald-trump/trump-slams-mueller-probe-one-year-anniversary-n874921" />
<item url="https://www.washingtontimes.com/topics/donald-trump/" />
<item url="https://m.huffpost.com/us/topic/donald-trump" />
<item url="http://metro.co.uk/tag/donald-trump/" />
<item url="https://www.thedailybeast.com/donald-trump-jr-called-blocked-number-before-and-after-trump-tower-meeting-with-russians" />
<item url="https://www.instagram.com/realdonaldtrump/?hl=en" />
<item url="https://m.economictimes.com/topic/Donald-Trump" />
<item url="http://www.bbc.com/news/world-asia-44136263" />
<item url="http://m.scmp.com/topics/donald-trump" />
<item url="http://www.nme.com/tag/donald-trump" />
<item url="https://www.thestar.com/news/donald-trump.html" />
<item url="https://news.sky.com/story/trump-well-have-to-see-if-n-korea-summit-still-on-11375068" />
<item url="http://m.france24.com/en/tag/donald-trump/" />
<item url="http://deadline.com/tag/donald-trump/" />
<item url="https://www.vanityfair.com/people/donald-trump" />
<item url="http://mobile.newsnow.co.uk/h/Current+Affairs/International/US/Donald+Trump" />
<item url="https://qz.com/1279771/there-are-186-things-donald-trump-jr-says-he-cant-remember-or-doesnt-know-about-the-trump-tower-meeting/" />
<item url="http://digg.com/channel/donaldtrump" />
<item url="http://nymag.com/daily/intelligencer/2018/05/michael-cohen-bribery-scandal-is-now-a-trump-bribery-scandal.html" />
<item url="https://m.imdb.com/name/nm0874339/" />
<item url="https://pagesix.com/tag/donald-trump/" />
<item url="http://www.tmz.com/person/donald-trump/" />
<item url="https://www.amazon.com/Donald-J-Trump-President-Other/dp/1621577872" />
<item url="http://www.complex.com/tag/donald-trump" />
<item url="https://ballotpedia.org/Donald_Trump" />
<item url="http://www.thejournal.ie/trump-doonbeg-2-4017626-May2018/" />
<item url="https://topics.nola.com/tag/donald%20trump" />
<item url="https://www.usmagazine.com/celebrities/donald-trump/" />
<item url="https://www.c-span.org/person/?donaldtrump" />
<item url="http://www.baltimoresun.com/topic/politics-government/donald-trump-PEBSL000163-topic.html" />
<item url="https://newsone.com/tag/donald-trump/" />
<item url="https://www.gq.com/about/donald-trump" />
<item url="http://mobi.perezhilton.com/category/donald-trump/" />
<item url="https://m.timesofindia.com/topic/Donald-Trump" />
<item url="https://www.factcheck.org/person/donald-trump/" />
<item url="http://m.spiegel.de/international/topic/donald_trump/" />
<item url="https://www.realclearpolitics.com/epolls/other/trump_favorableunfavorable-5493.html" />
<item url="https://mashable.com/category/donald-trump/" />
<item url="https://www.psychologytoday.com/us/basics/president-donald-trump" />
<item url="https://www.marketwatch.com/story/rex-tillerson-took-another-veiled-shot-at-donald-trump-2018-05-16" />
<item url="https://projects.propublica.org/politwoops/user/realDonaldTrump" />
<item url="https://www.dailydot.com/topics/donald-trump/" />
<item url="https://nypost.com/tag/donald-trump/" />
<item url="https://projects.fivethirtyeight.com/trump-approval-ratings/" />
<item url="http://www.foxnews.com/politics/2018/05/16/donald-trump-jr-s-meeting-with-russian-lawyer-what-to-know.html" />
<item url="https://www.bravenewfilms.org/16women" />
<item url="https://www.stuff.co.nz/world/americas/103952191/donald-trump-pays-hospital-visit-to-recovering-first-lady-melania" />
<item url="https://www.snopes.com/tag/donald-trump/" />
<item url="https://uproxx.com/topic/donald-trump/" />
<item url="https://www.investopedia.com/trump/" />
<item url="https://m.mic.com/topic/donald-trump" />
<block type="relatedSearches" url="">
<item url="" title="donald trump twitter" />
<item url="" title="donald trump education" />
<item url="" title="donald trump spouse" />
<item url="" title="donald trump age" />
<item url="" title="donald trump news" />
<item url="" title="donald trump wiki" />
<item url="" title="donald trump jr" />
<item url="" title="donald trump wife" />
</block>
</section>
<section col="right" />
</searchResult>
Finaly we select the data like
seelct nodename(block/item),url,type,title
Please help....asap
May 18, 2018 at 8:35 am
You appear to have a structure problem in your XML, as there are no block tags for all the item lines between the TwiitterCards block and the relatedSearches block. Not sure how you could navigate such a hierarchy using either the .nodes or .values functionality in T-SQL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 19, 2018 at 12:58 am
This should get you started
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<searchResult searchEngine="106" keyword="donald Trump" date="2018-05-18T09:00:34Z">
<section col="main">
<block type="topStories" url="">
<item url="https://www.theguardian.com/us-news/2018/may/18/bill-gates-donald-trump-difference-hiv-hpv" title="The Guardian ?? 5 hours ago" />
<item url="https://www.theguardian.com/us-news/2018/may/17/north-korea-trump-latest-warning-kim-jong-un-gaddafi" title="The Guardian ?? 13 hours ago" />
<item url="https://www.cnn.com/2018/05/17/politics/trump-planned-parenthood-funds/index.html" title="CNN.com" />
<item url="https://m.huffingtonpost.co.uk/entry/bil-gates-donald-trump_uk_5afe6f52e4b0a046186a09ad" title="HuffPost UK" />
</block>
<block type="knowledgeGraph" url="" />
<block type="videos" url="">
<item url="https://m.youtube.com/watch?v=ceNlAXhEfcE" title="Donald Trump Mocks Robert Mueller Investigation With ..." />
<item url="https://m.youtube.com/watch?v=QNLwyVxleQo" title="Donald Trump Remembered His Anniversary" />
</block>
<block type="twitterCards" url="https://twitter.com/realDonaldTrump?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Eauthor">
<item url="https://twitter.com/realDonaldTrump/status/997239623268085761?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997238932311068674?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997233764689498112?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997227223638790144?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
<item url="https://twitter.com/realDonaldTrump/status/997220260259487744?ref_src=twsrc%5Egoogle%7Ctwcamp%5Eserp%7Ctwgr%5Etweet" title="" />
</block>
<item url="https://en.m.wikipedia.org/wiki/Donald_Trump" />
<item url="http://www.donaldjtrump.com/" />
<item url="https://www.telegraph.co.uk/donald-trump/" />
<item url="https://www.theatlantic.com/magazine/archive/2017/10/will-donald-trump-destroy-the-presidency/537921/" />
<item url="http://time.com/donald-trump-after-hours/" />
<item url="http://mobile.nytimes.com/2017/12/09/us/politics/donald-trump-president.html" />
<item url="http://thehill.com/people/donald-trump" />
<item url="https://www.whitehouse.gov/people/donald-j-trump/" />
<item url="https://www.mirror.co.uk/all-about/donald-trump" />
<item url="https://www.standard.co.uk/topic/donald-trump" />
<item url="https://m.facebook.com/DonaldTrump/" />
<item url="https://www.politico.com/news/donald-trump" />
<item url="https://www.express.co.uk/latest/donald-trump" />
<item url="https://www.thesun.co.uk/who/donald-trump/" />
<item url="http://time.com/donald-trump-after-hours/" />
<item url="https://www.ft.com/donald-trump" />
<item url="https://www.theatlantic.com/magazine/archive/2017/10/will-donald-trump-destroy-the-presidency/537921/" />
<item url="https://www.cnet.com/news/donald-trump-weighs-in-laurel-or-yanny-white-house/" />
<item url="https://www.biography.com/people/donald-trump-9511238" />
<item url="https://m.huffingtonpost.co.uk/news/donald-trump/" />
<item url="http://www.nytimes.com/topic/person/donald-trump" />
<item url="http://mobile.nytimes.com/2017/12/09/us/politics/donald-trump-president.html" />
<item url="https://abcnews.go.com/topics/news/donald-trump.htm" />
<item url="https://www.aljazeera.com/topics/people/donald-trump.html" />
<item url="http://www.dailymail.co.uk/news/donald_trump/index.html" />
<item url="http://people.com/tag/donald-trump/" />
<item url="https://www.usatoday.com/story/opinion/2018/05/16/donald-trump-supporters-across-america-hes-delivering-his-agenda/606334002/" />
<item url="https://www.thetimes.co.uk/topic/donald-trump" />
<item url="https://www.forbes.com/profile/donald-trump/" />
<item url="http://www.politifact.com/personalities/donald-trump/" />
<item url="https://www.trump.com/" />
<item url="https://www.cbsnews.com/news/senate-judiciary-releases-trump-tower-meeting-hearing-transcripts-live-updates/" />
<item url="http://mobile.abc.net.au/news/2018-05-18/donald-trump-latest-on-the-russia-investigation-one-year-in/9774822" />
<item url="http://www.msnbc.com/topics/donald-trump" />
<item url="http://www.latimes.com/topic/politics-government/donald-trump-PEBSL000163-topic.html" />
<item url="http://www.bbc.co.uk/news/topics/cp7r8vgl2lgt/donald-trump" />
<item url="https://globalnews.ca/tag/donald-trump/" />
<item url="https://www.vox.com/2018/5/16/17362870/trump-immigrants-animals-ms-13-illegal" />
<item url="https://www.washingtonpost.com/news/the-fix/wp/2018/05/16/54-things-donald-trump-jr-couldnt-recall-or-remember-in-his-testimony/?utm_term=.70231c4e9af8" />
<item url="https://www.bloomberg.com/view/articles/2018-05-17/sex-isn-t-everything-in-trump-s-big-reveal-timothy-l-o-brien" />
<item url="https://www.rollingstone.com/topic/donald-trump" />
<item url="http://www.businessinsider.com/bill-gates-donald-trump-hiv-hpv-2018-5" />
<item url="http://www.chicagotribune.com/topic/politics-government/donald-trump-PEBSL000163-topic.html" />
<item url="https://www.haaretz.com/whdcMobileSite/middle-east-news/.premium-the-day-donald-trump-was-crowned-king-in-jerusalem-1.6092216" />
<item url="https://www.nbcnews.com/politics/donald-trump/trump-slams-mueller-probe-one-year-anniversary-n874921" />
<item url="https://www.washingtontimes.com/topics/donald-trump/" />
<item url="https://m.huffpost.com/us/topic/donald-trump" />
<item url="http://metro.co.uk/tag/donald-trump/" />
<item url="https://www.thedailybeast.com/donald-trump-jr-called-blocked-number-before-and-after-trump-tower-meeting-with-russians" />
<item url="https://www.instagram.com/realdonaldtrump/?hl=en" />
<item url="https://m.economictimes.com/topic/Donald-Trump" />
<item url="http://www.bbc.com/news/world-asia-44136263" />
<item url="http://m.scmp.com/topics/donald-trump" />
<item url="http://www.nme.com/tag/donald-trump" />
<item url="https://www.thestar.com/news/donald-trump.html" />
<item url="https://news.sky.com/story/trump-well-have-to-see-if-n-korea-summit-still-on-11375068" />
<item url="http://m.france24.com/en/tag/donald-trump/" />
<item url="http://deadline.com/tag/donald-trump/" />
<item url="https://www.vanityfair.com/people/donald-trump" />
<item url="http://mobile.newsnow.co.uk/h/Current+Affairs/International/US/Donald+Trump" />
<item url="https://qz.com/1279771/there-are-186-things-donald-trump-jr-says-he-cant-remember-or-doesnt-know-about-the-trump-tower-meeting/" />
<item url="http://digg.com/channel/donaldtrump" />
<item url="http://nymag.com/daily/intelligencer/2018/05/michael-cohen-bribery-scandal-is-now-a-trump-bribery-scandal.html" />
<item url="https://m.imdb.com/name/nm0874339/" />
<item url="https://pagesix.com/tag/donald-trump/" />
<item url="http://www.tmz.com/person/donald-trump/" />
<item url="https://www.amazon.com/Donald-J-Trump-President-Other/dp/1621577872" />
<item url="http://www.complex.com/tag/donald-trump" />
<item url="https://ballotpedia.org/Donald_Trump" />
<item url="http://www.thejournal.ie/trump-doonbeg-2-4017626-May2018/" />
<item url="https://topics.nola.com/tag/donald%20trump" />
<item url="https://www.usmagazine.com/celebrities/donald-trump/" />
<item url="https://www.c-span.org/person/?donaldtrump" />
<item url="http://www.baltimoresun.com/topic/politics-government/donald-trump-PEBSL000163-topic.html" />
<item url="https://newsone.com/tag/donald-trump/" />
<item url="https://www.gq.com/about/donald-trump" />
<item url="http://mobi.perezhilton.com/category/donald-trump/" />
<item url="https://m.timesofindia.com/topic/Donald-Trump" />
<item url="https://www.factcheck.org/person/donald-trump/" />
<item url="http://m.spiegel.de/international/topic/donald_trump/" />
<item url="https://www.realclearpolitics.com/epolls/other/trump_favorableunfavorable-5493.html" />
<item url="https://mashable.com/category/donald-trump/" />
<item url="https://www.psychologytoday.com/us/basics/president-donald-trump" />
<item url="https://www.marketwatch.com/story/rex-tillerson-took-another-veiled-shot-at-donald-trump-2018-05-16" />
<item url="https://projects.propublica.org/politwoops/user/realDonaldTrump" />
<item url="https://www.dailydot.com/topics/donald-trump/" />
<item url="https://nypost.com/tag/donald-trump/" />
<item url="https://projects.fivethirtyeight.com/trump-approval-ratings/" />
<item url="http://www.foxnews.com/politics/2018/05/16/donald-trump-jr-s-meeting-with-russian-lawyer-what-to-know.html" />
<item url="https://www.bravenewfilms.org/16women" />
<item url="https://www.stuff.co.nz/world/americas/103952191/donald-trump-pays-hospital-visit-to-recovering-first-lady-melania" />
<item url="https://www.snopes.com/tag/donald-trump/" />
<item url="https://uproxx.com/topic/donald-trump/" />
<item url="https://www.investopedia.com/trump/" />
<item url="https://m.mic.com/topic/donald-trump" />
<block type="relatedSearches" url="">
<item url="" title="donald trump twitter" />
<item url="" title="donald trump education" />
<item url="" title="donald trump spouse" />
<item url="" title="donald trump age" />
<item url="" title="donald trump news" />
<item url="" title="donald trump wiki" />
<item url="" title="donald trump jr" />
<item url="" title="donald trump wife" />
</block>
</section>
<section col="right" />
</searchResult>';
;WITH BASE_DATA AS
(
SELECT
BLCK.DATA.value('@type','VARCHAR(50)' ) AS BLCK_TYPE
,BLCK.DATA.value('@url' ,'VARCHAR(500)') AS BLCK_URL
,BLCK.DATA.query('*') AS BLCK_XML
FROM @TXML.nodes('searchResult/section/block') BLCK(DATA)
)
SELECT
BD.BLCK_TYPE
,BD.BLCK_URL
,ITEM.DATA.value('@url' ,'VARCHAR(500)') AS ITEM_URL
,ITEM.DATA.value('@title','VARCHAR(100)') AS ITEM_TITLE
FROM BASE_DATA BD
OUTER APPLY BD.BLCK_XML.nodes('item') ITEM(DATA);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply