How to join between physical table and xml data

Question

from xml data i am inserting data into physical table and as well inserting data into temp table just to hold the ID which is identity field.
Now i want to join between xml and physical table.

see my code which is working.
DECLARE @XmlData AS XML=N'<?xml version="1.0" encoding="utf-16"?>
<Root>
<PeriodicalData>
<Section>Consensus Model</Section>
<LineItem>Total Revenue</LineItem>
<XFundCode>TRIN</XFundCode>
<StandardDate>2010 FY</StandardDate>
<StandardValue>65225</StandardValue>
</PeriodicalData>
<PeriodicalData>
<Section>Consensus Model</Section>
<LineItem>Net Sales</LineItem>
<XFundCode>TRIN</XFundCode>
<StandardDate>2011 FY</StandardDate>
<StandardValue>108249</StandardValue>
</PeriodicalData>
</Root>'

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 '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 '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  

Now i want to join between tblSectionTemplate table and xml data by section name and also want to join TblLineItemTemplate and xml data by LineItem name.

1) join between tblSectionTemplate and @XmlData between by section field
2) and join between TblLineItemTemplate and @XmlData between by LineItem field

because i need to store section id and line item id in another table. so joining between table and xml data by section & LineItem field help me to get sectionID and LineItemID with their name like section and lineitem.

please show me how could i join between XmlData and tblSectionTemplate and TblLineItemTemplate table by section and LineItem field.
Thanks

Answer 1

Based on evolutionised details, check an example that, however, uses two tables only instead of four ones, using local tables (which probably is irrelevant):

declare @XmlData as xml = N'<?xml version="1.0" encoding="utf-16"?>
<Root>
    <PeriodicalData>
        <Section>Consensus Model</Section>
        <LineItem>Total Revenue</LineItem>
        <XFundCode>TRIN</XFundCode>
        <StandardDate>2010 FY</StandardDate>
        <StandardValue>65225</StandardValue>
        </PeriodicalData>
    <PeriodicalData>
        <Section>Consensus Model</Section>
        <LineItem>Net Sales</LineItem>
        <XFundCode>TRIN</XFundCode>
        <StandardDate>2011 FY</StandardDate>
        <StandardValue>108249</StandardValue>
    </PeriodicalData>
</Root>'


declare @tblSectionTemplate as table (SectionId int, Section nvarchar(max))
declare @TblLineItemTemplate as table (LineItemId int, LineItem nvarchar(max))

insert @tblSectionTemplate values (100, 'Consensus Model') 

insert @TblLineItemTemplate values (40, 'Total Revenue')
insert @TblLineItemTemplate values (50, 'Net Sales')


select SectionId, x.Section, 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 @tblSectionTemplate as st on st.Section = x.Section
inner join @TblLineItemTemplate as lit on lit.LineItem = x.LineItem

/*

SectionId   Section          LineItemId   LineItem        StandardDate
100        Consensus Model   50           Net Sales       2011 FY
100        Consensus Model   40           Total Revenue   2010 FY

*/

Answer 2

I am not sure that I understand your question, and as Yitzhak remarked, we don't have your permanent tables.

However, it seems that the question you really are asking is "how can map the generated IDENTITY values to the source values in my XML document when I don't insert them to the table". (Although there is a risk that I am misunderstanding you.)

You cannot do this with the OUTPUT clause in the INSERT statement, because there you only have access to the columns you actually inserted, not other columns in the source. However, there are great news: you can do this with the MERGE statement, at the price of a little more complicated syntax.

Since I don't understand your example, I cooked up my own. I hope you can study it and apply it on your own case. The scenario in my example is this: we have an XML document with order data, and there is an order ID in that data. But that is only a temporary value which we have no reason to store as such. To be able to store both header and details, we need a mapping, and to this end I use the MERGE statement with the odd condition 1 = 0 in the USING clause and there is only one branch for WHEN NOT MATCHED. I use the OUTPUT clause, and you can see that I insert both order ids.

CREATE TABLE #orders (OurOrderID int IDENTITY NOT NULL PRIMARY KEY,
                      CustomerID   nchar(5)   NOT NULL,
                      OrderDate    date       NOT NULL,
                      EmployeeID   int        NOT NULL)

CREATE TABLE #details (OurOrderID int           NOT NULL,
                       ProductID  int           NOT NULL,
                       Price      decimal(10,2) NOT NULL,
                       Qty        int           NOT NULL,
                       PRIMARY KEY (OurOrderID, ProductID))

CREATE TABLE #orderidmap (OurOrderID   int NOT NULL PRIMARY KEY,
                          TheirOrderID int NOT NULL UNIQUE)


DECLARE @x xml
SELECT @x =
  N'<Orders>
      <Order OrderID="13000" CustomerID="ALFKI"
             OrderDate="2006-09-20Z" EmployeeID="2">
         <OrderDetails ProductID="76" Price="123" Qty = "10"/>
         <OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
      </Order>
      <Order OrderID="13001" CustomerID="VINET"
             OrderDate="2006-09-20Z" EmployeeID="1">
         <OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
      </Order>
    </Orders>'

; WITH OrderData AS (
   SELECT TheirOrderID = T.Item.value('@OrderID[1]',    'int'),
          CustomerID   = T.Item.value('@CustomerID[1]', 'nchar(5)'),
          OrderDate    = T.Item.value('@OrderDate[1]',  'datetime'),
          EmployeeID   = T.Item.value('@EmployeeID[1]', 'smallint')
   FROM   @x.nodes('/Orders/Order') AS T(Item)
)
MERGE #orders o
USING OrderData OD ON 1 = 0
WHEN NOT MATCHED THEN
   INSERT(CustomerID, OrderDate, EmployeeID)  
      VALUES(OD.CustomerID, OD.OrderDate, OD.EmployeeID)
OUTPUT inserted.OurOrderID, OD.TheirOrderID INTO #orderidmap (OurOrderID, TheirOrderID)

; WITH Details AS (
   SELECT TheirOrderID = A.Item.value('@OrderID[1]',    'int'),
          ProductID    = B.Item.value('@ProductID[1]',  'smallint'),
          Price        = B.Item.value('@Price[1]',      'decimal(10,2)'),
          Qty          = B.Item.value('@Qty[1]',        'int')
   FROM   @x.nodes('/Orders/Order') AS A(Item)
   CROSS  APPLY A.Item.nodes('OrderDetails') AS B (Item)
) 
INSERT #details (OurOrderID, ProductID, Price, Qty)
   SELECT m.OurOrderID, D.ProductID, D.Price, D.Qty
   FROM   Details D
   JOIN   #orderidmap m ON D.TheirOrderID = m.TheirOrderID

go
SELECT * FROM #orders
SELECT * FROM #orderidmap
SELECT * FROM #details
go
DROP TABLE #orders, #details, #orderidmap