i have xml source. xml source having lacks of records. i
want extract/retrive xml source data using sql query.
please tell me how to write query.
Answers were Sorted based on User's Feedback
Answer / jagadeesh
create procedure proc_es
as
begin
declare @hdoc int
DECLARE @xml xml
set @xml=(select *
From openrowset(Bulk'C:\emp18.xml', single_blob) as xmldata)
exec sp_xml_preparedocument @hdoc output,@xml
insert into emp19(emp_id,emp_fname,emp_lname,emp_mail)
select *
from openxml(@hdoc,'/employee/details',3)
with(emp_id int,emp_firstname varchar(50),emp_lastname varchar(50),emp_mailid varchar(50))
exec sp_xml_removedocument @hdoc
end
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mathivanan
select extractvalue(columnname,'/roottag/subtag') from tablename
Is This Answer Correct ? | 0 Yes | 0 No |
What are the operators in sql?
Where is all the data on the internet stored?
Determine if oracle date is on a weekend?
What are instead of triggers?
What can be a size of a pl/sql block? Is there any limit?
How do I sort a table in sql?
what are local and global variables and their differences? : Sql dba
declare l1 number := null; l2 number :=null; begin if l1=l2 then message('equal'); else if l1<>l2 then message('not equal'); else message('else'); end if; end if; end; What will be the output ?
how to retrieve the top 2 salaried persons from a database?
Why do we use partitions in sql?
How are multiple column = value pairs delimited in the SET clause of an UPDATE statement? 1. With commas (SET price = 0, status = 'I') 2. With parentheses (SET (price = 0) (status = 'I')) 3. With double-pipes (SET price = 0 || status = 'I') 4. With square-brackets (SET [price = 0] [status = 'I'] 5. With single or multiple spaces (SET price = 0 status = 'I')
How to display Row Number with Records in Oracle SQL Plus?