Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB.NET
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB.NET General VB.NET discussions for issues that don't fall into other VB.NET forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 14th, 2004, 07:23 AM
Registered User
 
Join Date: Dec 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating DB Table using XML inline schema info

Hi,

      How are you da ? Iam doing fine here. I have created a Vb.NET program which will retrieve MS Access Database table fields.The program will then generate an Xml file using ADO dataset. In the created Xml file,I will have inline schema. This is the sample Xml file content.

- <NewDataSet>
- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="NewDataSet" msdata:IsDataSet="true">
- <xs:complexType>
- <xs:choice maxOccurs="unbounded">
- <xs:element name="rows">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="sno" type="xs:string" minOccurs="0" />
  <xs:element name="name" type="xs:string" minOccurs="0" />
  <xs:element name="city" type="xs:string" minOccurs="0" />
  <xs:element name="zip" type="xs:string" minOccurs="0" />
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
- <rows>
  <sno>1</sno>
  <name>Antony</name>
  <city>Bangalore</city>
  <zip>560040</zip>
  </rows>
- <rows>
  <sno>2</sno>
  <name>Rathore</name>
  <city>Chennai</city>
  <zip>600020</zip>
  </rows>
- <rows>
 </NewDataSet>
    Now what I need to do is, I have to create a new MS Access database table. The table column name and its data type should be created with the inline schema information. I n the inline schema of the above Xml file, we have the field name and its corresponding datatype. This will be the information for the database table to be created newly. Can any of you give me a source code of how to do this ? Because Iam struggling to create this. I need your help. Take care.All the best.Bye.

cheers,
SriRam.


Reply With Quote
  #2 (permalink)  
Old September 7th, 2012, 02:08 PM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default XML decoding in TSQL

I wrote this to for moving data from one server to another. It uses the XMLSCHEMA along with the XML DATA and then, on the receiving side, it can then create the table if it doesn't exist and/or Merge the data based on the Primary Key of the Table.

Code:
USE dbaadmin
GO

SET NOCOUNT ON
IF OBJECT_ID('tempdb..#XMLSCHEMA')	IS NOT NULL	DROP TABLE #XMLSCHEMA
IF OBJECT_ID('tempdb..##Source')	IS NOT NULL	DROP TABLE ##Source

DECLARE			@XML		XML
				,@XSL		XML
				,@TSQL		nVarChar(4000)
				,@TableName	sysname
			
CREATE TABLE	#XMLSCHEMA
				([name]		sysname NULL
				,[use]		sysname NULL
				,[type]		sysname NULL
				,[base]		sysname NULL
				,[value1]	sysname NULL
				,[value2]	sysname NULL)
------------------------------------------------------------------------
------------------------------------------------------------------------
--	GENERATE A XML CHUNK TO PLAY WITH
------------------------------------------------------------------------
------------------------------------------------------------------------
SET			@XML		=  
(SELECT * From dbaadmin.dbo.DBA_DBInfo FOR XML AUTO,XMLSCHEMA,ROOT('Table'))

SELECT @XML
------------------------------------------------------------------------
------------------------------------------------------------------------
--GET ONLY THE TOP XDS PORTION OF THE XML CHUNK
------------------------------------------------------------------------
------------------------------------------------------------------------
SELECT		@XSL		= x.query('.') 
FROM		@XML.nodes('/Table/*[1]') a(x)

-- COULD NOT USE REPLACE FUNCTION AS IT DOES NOT 
-- WORK FOR LARGE TABLES WHERE XML IS LARGER THAN 8000
-- EVEN THOUGH REPLACE DOES ACCEPT VARCHAR(MAX)
-- WOULD LIKE TO FIND XQUERY VERSION OF REPLACE
-- SELECT		@XML		= CAST(REPLACE(CAST(@XML AS VarChar(max)),CAST(@XSL AS VarChar(max)),'') AS XML)

SELECT		@XML =	(
					SELECT		CAST(STUFF	(
											CAST(@XML.query('*[1]/*') AS VarChar(max))
											,1
											,CHARINDEX	(
														'</xsd:schema>'
														,CAST(@XML.query('*[1]/*') AS VarChar(max))
														)+12
											,''
											) AS XML)
					FOR XML RAW ('Table')
					)


------------------------------------------------------------------------
------------------------------------------------------------------------
--	GET THE TABLE NAME
------------------------------------------------------------------------
------------------------------------------------------------------------
SELECT		@TableName	= a.x.value('*[2]/@name','sysname')
FROM		@XSL.nodes('*') a(x)

SELECT		@TableName [TableName]

------------------------------------------------------------------------
------------------------------------------------------------------------
--	POPULATE THE XMLSCHEMA TEMP TABLE
------------------------------------------------------------------------
------------------------------------------------------------------------
INSERT INTO	#XMLSCHEMA
SELECT		a.x.value('@name','sysname') [name]
			,a.x.value('@use','sysname') [use] -- use="required" for PK of Source
			,a.x.value('@type','sysname') [type]
			,a.x.value('*[1]/*[1]/@base','sysname') [base]
			,a.x.value('*[1]/*[1]/*[1]/@value','sysname') [value1]
			,a.x.value('*[1]/*[1]/*[2]/@value','sysname') [value2]
FROM		@XSL.nodes('/*/*/*/*') a(x)

------------------------------------------------------------------------
------------------------------------------------------------------------
--	CREATE THE DYNAMIC SQL TO CREATE THE SOURCE TEMP TABLE FROM THE XSD
------------------------------------------------------------------------
------------------------------------------------------------------------
SET			@TSQL		= 'SELECT		'
SELECT		@TSQL		= @TSQL -- START BUILDING NEXT LINE OF QUERY
						+ 'a.x.value(''@'+[name]+''','''
						+REPLACE(COALESCE([type],[base]+'('+value1+COALESCE(','+nullif(value2,'')+')',')')),'sqltypes:','')+''') ' 
						+ QUOTENAME([name])
						+ CHAR(13) + CHAR(10) + '			,' 
FROM		#XMLSCHEMA ColumnData
			
SET			@TSQL		= REPLACE	(
									@TSQL+'||'			--APPEND DOUBLE PIPE TO END SO I CAN IDENTIFY LAST COMMA 
									,'			,||'	-- REPLACE LINE WITH LAST COMMA WITH NEXT VALUE
									,'INTO ##Source'+CHAR(13) + CHAR(10)+'FROM		@XML.nodes(''/Table/*'') a(x)'
									)

------------------------------------------------------------------------
------------------------------------------------------------------------
--	CREATE THE SOURCE TEMP TABLE FROM THE XSD
------------------------------------------------------------------------
------------------------------------------------------------------------
EXEC		sp_Executesql @TSQL,N'@XML XML',@XML

------------------------------------------------------------------------
------------------------------------------------------------------------
--	SHOW THE SOURCE DATA AFTER INSERTED INTO THE TEMP TABLE
------------------------------------------------------------------------
------------------------------------------------------------------------
SELECT * FROM ##Source

------------------------------------------------------------------------
------------------------------------------------------------------------
--	CREATE THE DYNAMIC SQL TO GENERATE THE MERGE STATEMENT
------------------------------------------------------------------------
------------------------------------------------------------------------

	-- CREATE DESTINATION IF IT DOES NOT ALREADY EXIST




------------------------------------------------------------------------
------------------------------------------------------------------------
--	CREATE THE DYNAMIC SQL TO GENERATE THE MERGE STATEMENT
------------------------------------------------------------------------
------------------------------------------------------------------------

	-- ONLY DO MERGE IF DESTINATION HAS A PRIMARY KEY

IF EXISTS	(
			SELECT		SIK.colid 
			FROM		sysindexkeys SIK 
			JOIN		sysobjects SO 
					ON	SIK.[id] = SO.[id]  
			WHERE		SIK.indid = 1
					AND	SO.ID = OBJECT_ID(@TableName)
			)
BEGIN

		SET			@TSQL		= 'MERGE INTO '+@TableName+' as Target' + CHAR(13) + CHAR(10) 
								+ 'USING ##Source as Source' + CHAR(13) + CHAR(10)
								+ 'ON' + CHAR(9)
				
		SELECT		@TSQL		= @TSQL + 'Target.['+[name]+'] = Source.['+[name]+']'+CHAR(13)+CHAR(10)+'AND'+CHAR(9)
		FROM		syscolumns 
		WHERE		[id] = OBJECT_ID(@TableName)
			AND		colid IN	(
								SELECT		SIK.colid 
								FROM		sysindexkeys SIK 
								JOIN		sysobjects SO 
										ON	SIK.[id] = SO.[id]  
								WHERE		SIK.indid = 1
										AND	SO.ID = OBJECT_ID(@TableName)
								)
				
		SET			@TSQL		= REPLACE(@TSQL+'||','AND'+CHAR(9)+'||',CHAR(13)+CHAR(10)+'when matched then update set'+CHAR(13)+CHAR(10)+'Target.')

		SELECT		@TSQL		= @TSQL + '['+[name]+']=Source.['+[name]+']'+CHAR(13)+CHAR(10)+',Target.'
		FROM		#XMLSCHEMA ColumnData

		SET			@TSQL		= REPLACE(@TSQL+'||',',Target.||',CHAR(13)+CHAR(10)+'when not matched then insert'+CHAR(13)+CHAR(10)+'(')

		SELECT		@TSQL		= @TSQL + '['+[name]+']'+CHAR(13)+CHAR(10)+','
		FROM		#XMLSCHEMA ColumnData

		SET			@TSQL		= REPLACE(@TSQL+'||',CHAR(13)+CHAR(10)+',||',')'+CHAR(13)+CHAR(10)+'values'+CHAR(13)+CHAR(10)+'(Source.')

		SELECT		@TSQL		= @TSQL	+ '['+[name]+']'+CHAR(13)+CHAR(10)+',Source.'
		FROM		#XMLSCHEMA ColumnData

		SET			@TSQL		= REPLACE(@TSQL+'||',CHAR(13)+CHAR(10)+',Source.||',');')


		------------------------------------------------------------------------
		------------------------------------------------------------------------
		--	SHOW THE MERGE STATEMENT
		------------------------------------------------------------------------
		------------------------------------------------------------------------
		PRINT		(@TSQL)
		------------------------------------------------------------------------
		------------------------------------------------------------------------
		--	RUN THE MERGE
		------------------------------------------------------------------------
		------------------------------------------------------------------------
		EXEC		(@TSQL)

END

------------------------------------------------------------------------
------------------------------------------------------------------------
--	CLEAN UP TEMP TABLES
------------------------------------------------------------------------
------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#XMLSCHEMA')	IS NOT NULL	DROP TABLE #XMLSCHEMA
IF OBJECT_ID('tempdb..##Source')	IS NOT NULL	DROP TABLE ##Source
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem creating temp XML SCHEMA COLLECTION bijgupt SQL Server 2005 0 May 4th, 2007 12:58 AM
How do I export an access db table to xml? solos VB How-To 4 April 4th, 2007 12:56 AM
creating a subset of xml schema using XSLT kapar_p XSLT 8 November 21st, 2006 09:46 AM
Store info into a MySQL DB table KoRn PHP How-To 1 February 17th, 2004 03:00 PM
Creating an XML schema dynamically with C# dr_doom_77 C# 0 July 5th, 2003 12:27 PM



All times are GMT -4. The time now is 04:16 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.