 |
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
December 14th, 2004, 07:23 AM
|
|
Registered User
|
|
Join Date: Dec 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
September 7th, 2012, 01:08 PM
|
|
Registered User
|
|
Join Date: Sep 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
|
