Click here to Skip to main content
12,451,425 members (49,795 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
How to convert Physical XML files into Sqlserver Tables?

* Physical XML need to be converted into XML table with respective columns
* XML file may contains multiple tables, in such case multiple tables has to be created in db

Please help me out...
Posted 10-Aug-12 12:32pm
Comments
Wes Aday 10-Aug-12 18:42pm
   
What have you tried?
Sergey Alexandrovich Kryukov 10-Aug-12 19:11pm
   
What is a non-physical XML file? Chemical? :-)
--SA
Kenneth Haugland 10-Aug-12 19:56pm
   
lol... perhaps he has an imaginary storage :-), but a DataTable has .ToXML and .LoadXML perhaps it is this he is searching for?
Sergey Alexandrovich Kryukov 10-Aug-12 19:12pm
   
What is your problem, exactly? ADO.NET? SQL? XML reading/parsing? It's all parts of .NET, no secret involved; just read MSDN help. If you have any particular question, please ask it.
--SA
guayasen_o 10-Aug-12 23:35pm
   
wich format u use on the XML? Is custom format or standard from any program?
   
The question makes little sense. What is a "format on XML". First, XML is itself a format. Based on it, there could be different instances of schema, according to one or another data model. Just learn the XML basics to understand it.
--SA
guayasen_o 18-Aug-12 19:57pm
   
OK, thanks, i will try to learn a little bit about XML (an another things).
Seems is not possible to try help here using incorrect words.
Sergey Alexandrovich Kryukov 18-Aug-12 22:10pm
   
Perhaps learning this is in your best interests...
--SA

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

SQLServer:
--CREATE SCHEMA [cpqa]		
USE [cpqaAnswers]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblFF]') AND type in (N'U'))
DROP TABLE [cpqa].[tblFF]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblFFIdx]') AND type in (N'U'))
DROP TABLE [cpqa].[tblFFIdx]
CREATE TABLE [cpqaAnswers].[cpqa].[tblFF](
	[xml_in][xml]										
	)
	
CREATE TABLE [cpqaAnswers].[cpqa].[tblFFIdx](
	[idx][int]IDENTITY(1,1) NOT NULL,
		[xml_in][xml]										
		)
INSERT INTO [cpqaAnswers].[cpqa].[tblFF]
 
SELECT * FROM OPENROWSET(BULK 'C:\Users\FF\xmlilliform.xml', SINGLE_BLOB) AS [whatever]
	
INSERT INTO [cpqaAnswers].[cpqa].[tblFFIdx]
	SELECT [xml_in] FROM [cpqaAnswers].[cpqa].[tblFF]
	
SELECT * FROM [cpqaAnswers].[cpqa].[tblFFIdx]	
xmlilliform.xml:
<root>
	<HEADER>
	<company_code>GULMOHAR</company_code>
	<batch_date>13/01/2011</batch_date>
	</HEADER>
	<kycdata>
	<app_updtflg>01</app_updtflg>
	<app_pos_code>GULMOHAR</app_pos_code>
	<app_type>I</app_type>
	<app_no>00001</app_no>
	<app_date>02/01/2012</app_date>
	<app_pan_no>ABBDE1234J</app_pan_no>
	<app_pan_copy>Y</app_pan_copy>
	<app_exmt>N</app_exmt>
	<app_exmt_cat />
	<app_exmt_id_proof>01</app_exmt_id_proof>
	<app_ipv_flag>Y</app_ipv_flag>
	<app_ipv_date>13/01/2012</app_ipv_date>
	<app_gen>M</app_gen>
	<app_name>SANDEEP CHAVAN</app_name>
	<app_f_name>HANMANT CHAVAN</app_f_name>
	<app_regno />
	<app_dob_incorp>05/10/1980</app_dob_incorp>
	<app_commence_dt />
	<app_nationality>01</app_nationality>
	<app_oth_nationality />
	<app_filler3 />
	</kycdata>
	<footer>
	<no_of_kyc_records>1</no_of_kyc_records>
	<no_of_addldata_records>0</no_of_addldata_records>
	</footer>
</root>	
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160826.1 | Last Updated 11 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100