SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server what XML part of query is doing for XML experts


SQL Server what XML part of query is doing for XML experts

Author
Message
Redmond01
Redmond01
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 221
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


Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)SSC Guru (128K reputation)

Group: General Forum Members
Points: 128209 Visits: 21798
Redmond01 - Thursday, October 12, 2017 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



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.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search