Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 migration oracle with XML to sql server

Author  Topic 

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-26 : 05:21:43
how to migrate oracle database having xml code to sql server database with xml code

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-26 : 09:29:00
Please post the oracle table definition and some sample XML data from Oracle.

BTW, you should be able to do this pretty easily with a SSIS package
Go to Top of Page

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-29 : 03:29:34
code is like below...

select xmlelement("menu",
xmlagg(
xmlelement("section",
xmlattributes(ams.text as "text",
ams.url as "url",
ams.image_url as "image_url"
),
xmlforest((select xmlagg(
xmlelement("item",
xmlattributes(itm.text as "text",
itm.url as "url",
itm.image_url as "image_url"
),
)
)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-29 : 09:35:44
ok. Could you also please post the results of this query when executed against Oracle?
Go to Top of Page

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-30 : 04:56:54
select xmlelement("errormessages",
( select xmlagg(
xmlelement("message", xmlattributes(error_no ,
description,
severity )
)
)
from tbl_error_messages
where application_id = application_id)
) as xml
from dual;

------------------------------------------------------------------

xml output as below...........

<errormessages><message ERROR_NO="-20001" DESCRIPTION="Error Ocurred while Execu
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 10:29:37
try something like this:


select error_no '@ERROR_NO'
, descr '@DESCRIPTION'
, severity '@SEVERITY'
from (select -20001, 'Error occurred ...', 1) v(error_no, descr, severity)
for xml path('message'), root('errormessages')
Go to Top of Page

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-01 : 01:49:32
thanks lot.. gbritton

regards
Taj
Go to Top of Page
   

- Advertisement -