如何将物理XML文件转换为Sqlserver表 [英] How to convert Physical XML files into Sqlserver Tables

问题描述

如何将物理XML文件转换为Sqlserver表？



*需要将物理XML转换为具有相应列的XML表

* XML文件可能包含多个表，在这种情况下，必须在db中创建多个表



请帮帮我...

解决方案

SQLServer：

  -  CREATE SCHEMA [cpqa] 
 USE [cpqaAnswers] 
 GO 
 IF EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N''[cpqa]。[tblFF]''）并输入（N''U''））
 DROP TABLE [cpqa]。[tblFF ] 
如果EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N''[cpqa]。[tblFFIdx]''）并键入（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 [无论如何] 
 
 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> 

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...

解决方案

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>	

这篇关于如何将物理XML文件转换为Sqlserver表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！