October 12, 2017 at 9:42 am
Can some one please tell what below query is doing specially the XML part in nested queries. I have put ---*** infront of line
where I have some confusion if you can comment to ----- that will be great
INSERT INTO QSch.QTab (IID, LLID, SVal)
SELECT IID, LLID, cast(f2.SVal as XML(QD.IIDFSVal)) ----------------------*** (what is cast ( f2.Sval as XML(QD.IIDFSVal)) means her
FROM (Select Distinct IID, LLID
from #tmpTableItems where FID is not null) f1
cross apply
(Select FID,
coalesce(v_value, 0) as SelVal,
case when combID > 0 then combID
else null
end as ComdIID
from #tmpTableItems f2 where FID is not null
and f2.IID = f1.IID and f2.LLID = f1.LLID
for XML RAW('feat'), ELEMENTS, TYPE, ROOT('FtSel')) as f2(SVal) -------***** what is line doing using XML
exec Build.Log 'Migration', 'QSVal'
drop table #tiItems
drop table #tmpTableItems
insert into QD.IIDFID (IID,LLID, FID)
Select T.IID, T.LLID, T.FID
from
(select q.IID,
q.LLID,
feat.value('(FID)[1]', 'varchar(50)') AS FID,
feat.value('(SelVal)[1]','int') as SelVal
FROM QSch.QTab
cross apply SVal.nodes('/FtSel') as S(selection) -------*** whats happening after cross apply
cross apply S.selection.nodes('feat') as F(feat) --------*** what happening after cross appply
) as T
where T.SelVal = 1
Group by T.IID, T.LLID, T.FID
October 12, 2017 at 10:13 am
Redmond01 - Thursday, October 12, 2017 9:42 AMCan some one please tell what below query is doing specially the XML part in nested queries. I have put ---*** infront of line
where I have some confusion if you can comment to ----- that will be greatINSERT INTO QSch.QTab (IID, LLID, SVal)
SELECT IID, LLID, cast(f2.SVal as XML(QD.IIDFSVal)) ----------------------*** (what is cast ( f2.Sval as XML(QD.IIDFSVal)) means her
FROM (Select Distinct IID, LLID
from #tmpTableItems where FID is not null) f1
cross apply
(Select FID,
coalesce(v_value, 0) as SelVal,
case when combID > 0 then combID
else null
end as ComdIID
from #tmpTableItems f2 where FID is not null
and f2.IID = f1.IID and f2.LLID = f1.LLID
for XML RAW('feat'), ELEMENTS, TYPE, ROOT('FtSel')) as f2(SVal) -------***** what is line doing using XMLexec Build.Log 'Migration', 'QSVal'
drop table #tiItems
drop table #tmpTableItemsinsert into QD.IIDFID (IID,LLID, FID)
Select T.IID, T.LLID, T.FID
from
(select q.IID,
q.LLID,
feat.value('(FID)[1]', 'varchar(50)') AS FID,
feat.value('(SelVal)[1]','int') as SelVal
FROM QSch.QTab
cross apply SVal.nodes('/FtSel') as S(selection) -------*** whats happening after cross apply
cross apply S.selection.nodes('feat') as F(feat) --------*** what happening after cross appply
) as T
where T.SelVal = 1
Group by T.IID, T.LLID, T.FID
The query creates an xml output by joining the table #tmpTableItems to itself using cross apply. The casting of f2.SVal to XML guarantees that the data type is correct for the insert.
The line "for XML RAW('feat'), ELEMENTS, TYPE, ROOT('FtSel')) as f2(SVal)" dictates the XML creation, the table alias and the column name.
The cross apply lines use the XML nodes internal table valued function to parse the XML previously generated and define the table alias and the column names.
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply