migration oracle with XML to sql server

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

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"
),
)
)

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?

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

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

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-10-01 : 01:49:32

thanks lot.. gbritton

regards
Taj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources