How to select data from XML data

Hi All,

how to select the data from XML data
My XML data like...,en_US,sc.html,en_US,sc.html

Finaly i want to select the data like

Select searchEngineId,keyword,date,position,url


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">
	<Subcategory ProductSubcategoryID = "18" Name="Bib-Shorts">
			<ProductID Type="1">855</ProductID>
			<ProductID Type="2">676</ProductID>
			<Name>Men's Bib-Short, s</Name>

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

Provide sample xml please for us to help you,en_US,sc.html,en_US,sc.html

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



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.



Use the ticks provided for you ```

The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
For beginners who are struggling to understand the basics of SQL Query, here is a brief discussion on the top query in Oracle and mysql: MS SQL Training Center in Noida