How to return all fields from xml using xquery

Question

I want to search data from xml and list all the fields from there without mentioning field name. when we write normal sql then we can write select * from mytable and same way i want to return all fields from xml data by xquery.

this way i tried but not working

declare @xml xml  
declare @li varchar(max) ='Segment Detail'  
declare @newXfundCode varchar(max) ='TEST'  
  
CREATE TABLE #tmpData (id INT, xmldata xml)    
  
INSERT INTO #tmpData(id,xmldata) values (1,N'<?xml version="1.0" encoding="utf-16"?>  
 <PWR_ViewAll>  
   <dgvViewAll_Vertical>  
     <Section_x0020_>ZB-P1</Section_x0020_>  
     <LineItem>B. Riley FBR Inc.</LineItem>  
     <Revise_x0020_Date>08-21-2020</Revise_x0020_Date>  
     <GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>  
   </dgvViewAll_Vertical>  
 <dgvViewAll_Vertical>  
     <Section_x0020_>CL</Section_x0020_>  
     <LineItem>Deutsche Bank</LineItem>  
     <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>  
     <GroupKey>Segment Detail~Total Revenue~TEST~NBM~~1~CL</GroupKey>  
   </dgvViewAll_Vertical>  
 </PWR_ViewAll>')  
  
SELECT @xml=xmldata from #tmpData where ID=1  
  
  
SELECT @xml.query('/PWR_ViewAll/dgvViewAll_Vertical')   
FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)  
WHERE  CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0   
  
Drop table #tmpData  

if you run my above script it return all the data but i used where filter there which is not working.

if i use select this way which is also not working & throwing error.
SELECT *
FROM @XML .nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)
WHERE CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0

please guide me what to change in xquery sql as a result it should return all fields and filter also should work.

thanks

Answer 1

Check this modification:

. . .  
SELECT col.query('.')   
FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical[contains(concat("~", GroupKey[1], "~"), concat("~", sql:variable("@li"), "~"))]') AS tab(col)  



  

Answer 2

Hi @Sudip Bhatt ，

The column 'col' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.

Please also check the following methods：

 declare @xml xml  
 declare @li varchar(max) ='Segment Detail'  
 declare @newXfundCode varchar(max) ='TEST'  
      
 CREATE TABLE #tmpData (id INT, xmldata xml)    
      
 INSERT INTO #tmpData(id,xmldata) values (1,N'<?xml version="1.0" encoding="utf-16"?>  
  <PWR_ViewAll>  
    <dgvViewAll_Vertical>  
      <Section_x0020_>ZB-P1</Section_x0020_>  
      <LineItem>B. Riley FBR Inc.</LineItem>  
      <Revise_x0020_Date>08-21-2020</Revise_x0020_Date>  
      <GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>  
    </dgvViewAll_Vertical>  
  <dgvViewAll_Vertical>  
      <Section_x0020_>CL</Section_x0020_>  
      <LineItem>Deutsche Bank</LineItem>  
      <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>  
      <GroupKey>Segment Detail~Total Revenue~TEST~NBM~~1~CL</GroupKey>  
    </dgvViewAll_Vertical>  
  </PWR_ViewAll>')  
      
 SELECT @xml=xmldata from #tmpData where ID=1  
  
 SELECT col.query('.')   
 FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)  
 WHERE  CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0   

For more details, please refer to：
nodes() Method (xml Data Type)

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November
1.What can I do if my transaction log is full?
https://learn.microsoft.com/en-us/answers/questions/159917/what-can-i-do-if-my-transaction-log-is-full-hot-is.html
2.How to convert Profiler trace into a SQL Server table
https://learn.microsoft.com/en-us/answers/questions/159946/how-to-convert-profiler-trace-into-a-sql-server-ta.html