Hi All I have an Oracle Query that i want to convert to sql server, i could manage the date conversions but still there are some XMLTABLE that i cannot wrap my head around, Can someone please help me what the XMLTABLE part is doing and what is the equivalent in SQL Server.
Thanks
ORACLE QUERY
SELECT aosp.osi_key,
Cast(aa.osi_data_type AS NUMBER(3)) AS osi_data_type,
aa.hour_ctr,
sp.uom_name
FROM air_so aosp,
Sap sp,
(WITH t
AS (SELECT 1 d1,
5 d2
FROM dual)
SELECT bb.hour_ctr,
d1 + i osi_data_type
FROM t,
XMLTABLE('for $i in 0 to xs:int(D) return $i' passing XMLELEMENT(
d, d2
-d1) COLUMNS i INTEGER path '.'),
(WITH t
AS (SELECT ( To_date('07/30/2012 07', 'MM/DD/YYYY HH24') -
Numtodsinterval(10, 'hour') ) d1
,
( To_date('09/19/2012 11', 'MM/DD/YYYY HH24')
+ Numtodsinterval(10, 'hour') )
d2
FROM dual)
SELECT i hour_ctr
FROM t,
XMLTABLE('for $i in 0 to xs:int(D) return $i' passing
XMLELEMENT
(d, (d2
-d1)*24) COLUMNS i INTEGER path '.')) bb) aa
WHERE sp.osi_key = aosp.osi_key
AND sp.active_flag = 1
ORDER BY 1,
2,
3
SQL SERVER
SELECT aosp.osi_key,
Cast(aa.osi_data_type AS NUMBER(3)) AS osi_data_type,
aa.hour_ctr,
sp.uom_name
FROM air_so aosp,
[dbo].[saP] sp
;WITH t
AS(SELECT 1 d1,
5 d2
)
SELECT bb.hour_ctr,
d1 + i osi_data_type
FROM t,
XMLTABLE('for $i in 0 to xs:int(D) return $i' passing XMLELEMENT(
d, d2
-d1) COLUMNS i INTEGER path '.'),
;WITH t
AS (select convert(varchar, dateadd(hour, -10,'07/30/2012'), 101) d1
,
convert(varchar, dateadd(hour, 10,'09/19/2012'), 101)
d2
)
SELECT i hour_ctr
FROM t,
XMLTABLE('for $i in 0 to xs:int(D) return $i' passing
XMLELEMENT
(d, (d2
-d1)*24) COLUMNS i INTEGER path '.')) bb) aa
WHERE sp.osi_key = aosp.osi_key
AND sp.active_flag = 1
ORDER BY 1,
2,
3