Create table from xml file
-
I have a xml file on which i have to operate. I am using following approach
Declare @c_cmd VARCHAR(255),@C_940IN_FILE varchar(100),@C_940IN_DIR varchar(100) Set @C_940IN_FILE ='S_outbound.xml' Set @C_940IN_DIR ='\\xceed\tech\' CREATE TABLE #TEMP_940 ( ROWDATA varchar(8000) Null ) SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + '''' + ' WITH (FIELDTERMINATOR = ''><'')' PRINT @c_cmd EXEC(@c_cmd) ----------------------- select * from #TEMP_940 Drop Table #TEMP_940. . It gives me temp table with all nodes, like<Case_Dimension> <unit_of_measure>PK</unit_of_measure> <quantity>6</quantity> <unit_length/> <unit_width/> <unit_height/> <dimension_measure>CM</dimension_measure> </Case_Dimension>Now i have to use a cursor for getting all the values of corresponding fields. Is there any better way? By which i get table which will create a temp table like, without using cursorunit_of_measure quantity ... .... .... so on --------------- -------- ______PK_______ ___6____ ... ... ....regards -
I have a xml file on which i have to operate. I am using following approach
Declare @c_cmd VARCHAR(255),@C_940IN_FILE varchar(100),@C_940IN_DIR varchar(100) Set @C_940IN_FILE ='S_outbound.xml' Set @C_940IN_DIR ='\\xceed\tech\' CREATE TABLE #TEMP_940 ( ROWDATA varchar(8000) Null ) SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + '''' + ' WITH (FIELDTERMINATOR = ''><'')' PRINT @c_cmd EXEC(@c_cmd) ----------------------- select * from #TEMP_940 Drop Table #TEMP_940. . It gives me temp table with all nodes, like<Case_Dimension> <unit_of_measure>PK</unit_of_measure> <quantity>6</quantity> <unit_length/> <unit_width/> <unit_height/> <dimension_measure>CM</dimension_measure> </Case_Dimension>Now i have to use a cursor for getting all the values of corresponding fields. Is there any better way? By which i get table which will create a temp table like, without using cursorunit_of_measure quantity ... .... .... so on --------------- -------- ______PK_______ ___6____ ... ... ....regardsHi :) I have read the question a couple of times, but I'm still having trouble understanding your query. So, let's verify what we got;
Hum Dum wrote:
I have a xml file on which i have to operate.
That's a physical file, located on the harddisk? I mean, it's not stored inside an SQL Server table or anything like that?
Hum Dum wrote:
Now i have to use a cursor for getting all the values of corresponding fields.
To make sure I understood that correctly; you're first reading the columns from that file, and you'd be fetching it's values with a cursor? Is it a requirement to use Sql to import the data, or would it be allowed to use C# or VB.NET? How will the import-process be started, does it get run by the server automatically, or does the user init the import?
Hum Dum wrote:
Is there any better way?
There might be, depending on your requirements and restrictions. If you're allowed to program a solution, I'd rather suggest the XmlDocument[^]-class. If it has to be done from Sql, I'd suggest converting the file from XML to (several?) CSV-files. Then again, if it's Sql Server, then you might even get away with creating a linked server to your file and
SELECT INTOthe destination table.I are Troll :suss:
-
Hi :) I have read the question a couple of times, but I'm still having trouble understanding your query. So, let's verify what we got;
Hum Dum wrote:
I have a xml file on which i have to operate.
That's a physical file, located on the harddisk? I mean, it's not stored inside an SQL Server table or anything like that?
Hum Dum wrote:
Now i have to use a cursor for getting all the values of corresponding fields.
To make sure I understood that correctly; you're first reading the columns from that file, and you'd be fetching it's values with a cursor? Is it a requirement to use Sql to import the data, or would it be allowed to use C# or VB.NET? How will the import-process be started, does it get run by the server automatically, or does the user init the import?
Hum Dum wrote:
Is there any better way?
There might be, depending on your requirements and restrictions. If you're allowed to program a solution, I'd rather suggest the XmlDocument[^]-class. If it has to be done from Sql, I'd suggest converting the file from XML to (several?) CSV-files. Then again, if it's Sql Server, then you might even get away with creating a linked server to your file and
SELECT INTOthe destination table.I are Troll :suss:
Eddy Vluggen wrote:
How will the import-process be started, does it get run by the server automatically
SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + ''''
- ' WITH (KEEPIDENTITY, FIELDTERMINATOR = ''><'', ROWTERMINATOR= ''<Product_Information>'')'
the above command will import xml data in to temp table #Temp_940 (see my original post). I just need to execute this command and i have the data of XML. Now i have to operate on this data row by row and insert it into another table. for that i have to use cursor.
Eddy Vluggen wrote:
I'd rather suggest the XmlDocument[^]-class.
I know and used it also. when i suggest to use C# my PM says "NO". :confused: "You must use SQL server." So, its not my choice :((
-
Eddy Vluggen wrote:
How will the import-process be started, does it get run by the server automatically
SELECT @c_cmd='BULK INSERT #TEMP_940 FROM '+'''' + @C_940IN_DIR + '\' + @C_940IN_FILE + ''''
- ' WITH (KEEPIDENTITY, FIELDTERMINATOR = ''><'', ROWTERMINATOR= ''<Product_Information>'')'
the above command will import xml data in to temp table #Temp_940 (see my original post). I just need to execute this command and i have the data of XML. Now i have to operate on this data row by row and insert it into another table. for that i have to use cursor.
Eddy Vluggen wrote:
I'd rather suggest the XmlDocument[^]-class.
I know and used it also. when i suggest to use C# my PM says "NO". :confused: "You must use SQL server." So, its not my choice :((
Hum Dum wrote:
for that i have to use cursor.
I presume you select statement will not do the job for you
Insert Tablename (columnames....)
select columnnames...
from #Temp_940Never underestimate the power of human stupidity RAH