0

I have the querie below written in t-sql but the xml part for me is not understandable, can anyone help me in converting this querie to HANA SQL?

    DECLARE @x XML  
SELECT @x=CONVERT(XML,'<root><s>' + REPLACE(SettingVal,',','</s><s>') + '</s></root>')  
FROM dbo.IIS_EPC_PRO_SETTINGS 
WHERE ModSetting='HRUSer'  
SELECT t0.empID [UserID], t0.lastName+', '+ t0.firstName [Employee]  
FROM dbo.OHEM t0 
INNER JOIN dbo.HEM6 t1 ON t0.empid=t1.empID 
INNER JOIN 
(  
SELECT T.c.value('.', 'nvarchar(10)') [RoleID]  
FROM @X.nodes('/root/s') T(c) 
) AS t2 ON t1.roleID=t2.RoleID 
WHERE t0.Active='Y' 
AND NOT EXISTS(SELECT t3.UserId FROM dbo.IIS_EPC_PRO_WFSESSIONS t3 WHERE t3.UserId=t0.empID) 
GROUP BY t0.empID, t0.lastName, t0.firstName 
ORDER BY t0.lastName, t0.firstName

Best Regards, Daniel

Daniel Carreira
  • 21
  • 1
  • 5
  • It looks like it's just doing some funky XML comma-splitter. Try https://stackoverflow.com/questions/27709088/hana-split-string perhaps – Charlieface Sep 14 '21 at 14:26
  • You need to simplify your code and leave only XML processing to make it clear. But as was said in the previous comment, this is a csv processing, which can be done in the same way with [`xmltable`](https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.05/en-US/2bb8ab119e384b5f94635696bb9f68b5.html) function. – astentx Sep 15 '21 at 13:06

0 Answers0