Need help converting from oracle to sql server

Question

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
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

Answer 1

Hi Beacon77-6500

The function XMLTABLE in Oracle import XML to a table. In SQL Server you can do the same but it's in a different method. See this example to understand it.

For the date convert problem, you must convert string to date and apply a desired format.

I hope bring some help
Best Regards

Answer 2

@Naomi Thanks , how is it connecting to d1 and d2?
Thanks

Answer 3

The second XMLTABLE similarly creates a numbered row for each hour between d1 and d2 in column hour_ctr:

The standard solution to this problem that works on any relational database engine is to use a table of numbers. That's simply a one-column tables with consecutive numbers. I have a shorter article on my web site that discusses this concept and gives some examples: https://www.sommarskog.se/Short%20Stories/table-of-numbers.html.

Answer 4

Hi Beacon77-6500

About the row's number read this function. sql-server-row_number-function

I Hope bring help to you
Best Regards