How to select data from XML data

1

Hi All,

how to select the data from XML data
My XML data like...

spam links removed by moderator

Finaly i want to select the data like

Select searchEngineId,keyword,date,position,url

Regards
pols

2

Looks like your xml got goggle up badly. you might want to use triple ticks ` before and after the xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE product-content SYSTEM "product-content">
<Subcategories>
	<Subcategory ProductSubcategoryID = "18" Name="Bib-Shorts">
	<Products>
		<Product>
			<ProductID Type="1">855</ProductID>
			<ProductID Type="2">676</ProductID>
			<Name>Men's Bib-Short, s</Name>
			<ProductNumber>SB-M891-S</ProductNumber>
			<ListPrice>89.9900</ListPrice>
			<ModifiedDate>2008-03-11T10:01:36.827</ModifiedDate>
		</Product>
	</Products>
	</Subcategory>
</Subcategories>
3

my xml is working good
i am writing some code like
and i get url and position but not date and keyword

DECLARE @fakenews XML
set @fakenews=(select xmldata from temp_xml)
select @fakenews
;with XML1 as
(
select T.N.value('@searchEngineId','varchar(100)') searchEngineId,
T.N.value('@keyword','varchar(100)') keyword,
T.N.value('(/searchResults/searchResult/@date)[1]','nvarchar(max)') date,
T.N.value('@position','varchar(100)') position,
T.N.value('(.)[1]', 'varchar(4000)') as url
from @fakenews.nodes('/searchResults/searchResult/') as T(N)
--outer apply @fakenews.nodes('/searchResults/') as X(Y)
)
--insert into temp_table(seid,keyword,date,position,url)
select distinct searchEngineId,keyword,date,position,url
from XML1
--where date='2019-07-25'
order by position

4

Provide sample xml please for us to help you

5

spam links removed by moderator

6

the forum is gobbling up the xml
add 3 ticks ``` in front and after the xml text

7

spam links removed by moderator

8

spam links removed by moderator

9

You need to put the data in a code block - so the forum recognizes it correctly. To do that - you need to start with 3 back-ticks and end with 3 back-ticks.

image

11

Use the ticks provided for you ```