March 17, 2006 at 9:31 am
Hi,
I need to read a XML file from a specific location from a stored proecedure, parse it and store into table columns.
=================
?xml version="1.0"?>
<feed1>
<Table name="Table1">
<column name ="col1">
<coldata>Col1Sample1</coldata>
<coldata>Col1Sample2</coldata>
<coldata>Col1Sample3</coldata>
</column>
<column name ="col2">
<coldata>Col2Sample1</coldata>
<coldata>Col2Sample2</coldata>
<coldata>Col2Sample3</coldata>
</column>
<column name ="col3">
<coldata>Col3Sample1</coldata>
<coldata>Col3Sample2</coldata>
<coldata>Col3Sample3</coldata>
</column>
</Table>
</feed1>
==================This would be my XML file format.
Rather than inserting the whole XML file into a single table column, My stored procedure should read this file and also parse it. In the parsing process, when a node like <table> is encountered, a create table stmt shld be intiatated, and similary when node like <column> is encountered, the value in this node will be the column header and so on.
The end result would be that, the stored procedure should end up creating a table structure in Database along with data as defined in XML File.
Table1
=========================
Col1 Col2 Col3
Col1Sample1 Col2Sample1 Col3Sample1
Col1Sample2 Col2Sample2 Col3Sample2
Col1Sample3 Col2Sample3 Col3Sample2
======================
Any help would be appreciated.
March 17, 2006 at 11:16 am
Your going to have to restructure your xml.
as you have under column you have a element named coldata when parsing xml since the names are the same sql does not know which one to choose , so it choses the first.
otherwise you can do like this.
declare @XML_Document varchar(4000)
declare @Handle int
set @XML_Document = '<?xml version="1.0"?>
<feed1>
<Table name="Table1">
<column name="col1">
<coldata>Col1Sample1</coldata>
<coldata1>Col1Sample2</coldata1>
<coldata2>Col1Sample3</coldata2>
</column>
<column name="col2">
<coldata>Col2Sample1</coldata>
<coldata1>Col2Sample2</coldata1>
<coldata2>Col2Sample3</coldata2>
</column>
<column name="col3">
<coldata>Col3Sample1</coldata>
<coldata1>Col3Sample2</coldata1>
<coldata2>Col3Sample3</coldata2>
</column>
</Table>
</feed1>'
exec sp_xml_preparedocument @Handle output, @XML_Document
select *
from openxml(@Handle,'/feed1/Table/column',2)
with (ColumnName varchar(50) '../@name',
ColName varchar(50) '@name',
coldata varchar(50) ,
coldata1 varchar(50) ,
coldata2 varchar(50)
)
exec sp_xml_removedocument @Handle
March 17, 2006 at 12:56 pm
Thanks for your response. It was helpful.
However, as indicated, the XML file needs to be programatically loaded from its folder path. This procedure would be scheduler based, and would be executed automatically. So, in your code snippet, instead of the following line
"set @XML_Document = '<?xml version="1.0"?>
<feed1>.........................."
this part should pick up the XML file from a specified file location. Eg: "c:\feedxml\Test1.XML".
Assuming the file is dynamically loaded...
In the following lines
"-----------select *
from openxml(@Handle,'/feed1/Table/column',2)
with (ColumnName varchar(50) '../@name',
ColName varchar(50) '@name',
coldata varchar(50) ,
coldata1 varchar(50) ,
coldata2 varchar(50) ------------"
How can we define the Root(eg feed1) and first level child(eg Table) node from the dynamically loaded file as specified in this statement
Eg: (@Handle,'/feed1/Table/column',2)
Similarly, how can we specify other attributes requiring the XML Nodes dynamically
Appreciate your further help on this.
Regards,
Satish
May 16, 2006 at 7:21 pm
OPENXML really can't do what you want it to do. It wants name-value pairs, and your XML doesn't provide them. Sure, it has names, and it has values, but they're not organized into tuples.
The right solution here is to build a component outside the database that interoperates with the file system to find the XML file and uses XSLT to transform it into a structure usable by OPENXML. You'll find that to be much more maintainable in the long run, as well.
Robert Rossney
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply