October 12, 2017 at 10:08 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:14 am
Duplicate of https://www.sqlservercentral.com/Forums/1902189/SQL-Server-what-XML-part-of-query-is-doing-for-XML-experts
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply