How to convert Physical XML files into Sqlserver Tables

Question

1.00/5 (2 votes)

See more:

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

Member 4006546

Add a Solution

Comments

[no name] 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?

Sergey Alexandrovich Kryukov 12-Aug-12 3:23am

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RedDk · Answer 1 · 2013-02-11T07:47:00

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>