How to join between xquery data and table data

Question

DECLARE @XmlData AS XML=N'


Consensus Model
Total Revenue
TRIN
2010 FY
65225


Consensus Model
Net Sales
TRIN
2011 FY
108249

'

XQuery return section and lineitem from xml

SELECT 
col.value('(Section/text())[1]', 'NVARCHAR(MAX)')  AS Section,
col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')  AS LineItem
FROM @XmlData.nodes('/Root/PeriodicalData') AS tab (col)

i have this two table tblSectionTemplate and TblLineItemTemplate

in the above xml has field called Section and LineItem. tblSectionTemplate table has field called Section and TblLineItemTemplate has field called LineItem.

so i like to join between tblSectionTemplate and @XmlData based on Section field and also join between TblLineItemTemplate and @XmlData based on LineItem field.

How can i do it sir.

this link is bit similar for my scenario https://www.sqlshack.com/filtering-xml-columns-using-xquery-in-sql-server/

in my case i have to join xquery data with two table in same sql. one with tblSectionTemplate table and TblLineItemTemplate table.

EDIT

I tried this but not working rather getting more data where two section should return but i am getting 4 section. so data getting duplicated.

SELECT s.[SectionID],
col.value('(Section/text())[1]', 'NVARCHAR(MAX)')  AS Section
/*col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')  AS LineItem*/
FROM #tmptblSectionTemplate s CROSS APPLY @XmlData.nodes('/Root/PeriodicalData') AS tab (col)
WHERE col.value('(Section/text())[1]', 'NVARCHAR(MAX)') = s.Section

How could i mention join on clause like xmldata section = tmptblSectionTemplate.Section ?

also i need to add one more table there which is TblLineItemTemplate. for TblLineItemTemplate join will be on lineitem field because
lineitem exist in table and also in xml.

it will be really helpful if some one please share a sample code to achieve my goal.

Thanks

EDIT

DECLARE @XmlData AS XML=N'


Consensus Model
Total Revenue
TRIN
2010 FY
65225


Consensus Model
Net Sales
TRIN
2011 FY
108249

'

IF OBJECT_ID('tempdb..#tmptblSectionTemplate') IS NOT NULL DROP TABLE #tmptblSectionTemplate 
 IF OBJECT_ID('tempdb..#TblLineItemTemplate') IS NOT NULL DROP TABLE #TblLineItemTemplate 

 CREATE TABLE #tmptblSectionTemplate(
     [SectionID] [int] NOT NULL,
     [TickerID] [nvarchar](10) NOT NULL,
     [Section] [nvarchar](max) NULL
 )

 CREATE TABLE #TblLineItemTemplate(
     [ID] [int] NOT NULL,
     [TickerID] [nvarchar](10) NOT NULL,
     [LineItem] [nvarchar](max) NULL,
     [XFundCode] [varchar](max) NULL
 )

 INSERT INTO [tblSectionTemplate]([TickerID],[Section])
     OUTPUT inserted.SectionID, inserted.TickerID,inserted.Section 
         INTO #tmptblSectionTemplate(SectionID, TickerID,Section)
     SELECT DISTINCT 'TER',
     col.value('(Section/text())[1]', 'NVARCHAR(MAX)')  AS Section
     FROM @XmlData.nodes('/Root/PeriodicalData') AS tab (col)

 INSERT INTO TblLineItemTemplate([TickerID],[LineItem],XFundCode)
     OUTPUT inserted.ID, inserted.TickerID,inserted.LineItem,inserted.XFundCode
         INTO #TblLineItemTemplate(ID, TickerID,LineItem,XFundCode)
     SELECT DISTINCT 'TER',
     col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')  AS LineItem,
     col.value('(XFundCode/text())[1]', 'NVARCHAR(MAX)')  AS v
     FROM @XmlData.nodes('/Root/PeriodicalData') AS tab (col)

 --SELECT * FROM #tmptblSectionTemplate
 --SELECT * FROM #TblLineItemTemplate


 --select 
 --st.SectionId, x.Section, lit.ID as LineItemID, x.LineItem, pd.value('StandardDate[1]', 'nvarchar(max)') as StandardDate  
 --from @XmlData.nodes('/Root/PeriodicalData') as r(pd)
 --cross apply (values (pd.value('Section[1]', 'nvarchar(max)'), pd.value('LineItem[1]', 'nvarchar(max)'))) as x(Section, LineItem)
 --inner join #tmptblSectionTemplate as st on st.Section = x.Section
 --inner join #TblLineItemTemplate as lit on lit.LineItem = x.LineItem

 SELECT s.[SectionID],t.ID as LineItemID,
col.value('(Section/text())[1]', 'NVARCHAR(MAX)')  AS Section,
col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')  AS LineItem
FROM tblSectionTemplate s join @XmlData.nodes('/Root/PeriodicalData') AS tab (col)
on col.value('(Section/text())[1]', 'NVARCHAR(MAX)') = s.Section
join TblLineItemTemplate t
on col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')=t.LineItem


Now run the script then can understand above select return lots of duplicate data. thanks

Answer

Hi @Sudip Bhatt ,

Please refer to:

create table tblSectionTemplate(SectionID int,Section char(25))  
create table TblLineItemTemplate(LineItemId int,LineItem char(25))  
insert tblSectionTemplate values (100, 'Consensus Model')   
insert TblLineItemTemplate values (40, 'Total Revenue'),(50, 'Net Sales')  
          
--select * from tblSectionTemplate  
--select * from TblLineItemTemplate  
  
DECLARE @XmlData AS XML=N'  
        
        
      Consensus Model  
      Total Revenue  
      TRIN  
      2010 FY  
      65225  
        
        
      Consensus Model  
      Net Sales  
      TRIN  
      2011 FY  
      108249  
        
      '  
          
SELECT s.[SectionID],  
      col.value('(Section/text())[1]', 'NVARCHAR(MAX)')  AS Section,  
	  t.LineItemId,  
      col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')  AS LineItem  
FROM tblSectionTemplate s join @XmlData.nodes('/Root/PeriodicalData') AS tab (col)  
on col.value('(Section/text())[1]', 'NVARCHAR(MAX)') = s.Section  
join TblLineItemTemplate t  
on col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)')=t.LineItem  
          
          
drop table tblSectionTemplate  
drop table TblLineItemTemplate

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 October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html

Share via

How to join between xquery data and table data

1 answer

Your answer