Cast SQL Data using XML

• Redmond01

  SSC-Addicted

  Points: 492

  More actions

  October 12, 2017 at 10:08 am

  #333258

  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

• Thom A

  SSC Guru

  Points: 99143

  More actions

  October 12, 2017 at 10:14 am

  #1963421

  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