Advertisement

02.16.2008 at 02:33AM PST, ID: 23168184
[x]
Attachment Details

How to create an XML Schema for a table in SQL Server 2005 using C#

Asked by pvrbicuity in Extensible Markup Language (XML), C# Programming Language, SQL Server 2005

Tags: C#, XML, Bulk Load, XML Schema

Hello,
I am an XML novice. I have an XML document as shown below:
<Snapshot subscriber = 1002 company = 001>
    <Snapshot-Table1>
        <row>
             <t1col1>row1 col1 data</t1col1>
             <t1col2>row1 col2 data</t1col2>
             <t1col3>row1 col3 data</t1col3>
       </row>
        <row>
             <t1col1>row2 col1 data</t1col1>
             <t1col2>row2 col2 data</t1col2>
             <t1col3>row2 col3 data</t1col3>
       </row>

    </Snapshot-Table1>
    <Snapshot-Table2>
        <row>
             <t2col1>row1 col1 data</t2col1>
             <t2col2>row1 col2 data</t2col2>
             <t2col3>row1 col3 data</t2col3>
             <t2col4>row 1 col4 data<t2col4>
       </row>
        <row>
             <t2col1>row2 col1 data</t2col1>
             <t2col2>row2 col2 data</t2col2>
             <t2col3>row2 col3 data</t2col3>
             <t2col4>row 1 col4 data<t2col4>
       </row>

    </Snapshot-Table2>
</Snapshot>
There will be multip SQL server dbs and I need to load the above into multiple tables in an SQL Server Db according to subscriber id and company id. The table structures also may be different across the databases. I can separate the doc into multiple documents according to the destination tables. To use the Bulk XML loading COM component I need to create an XML Schema xml document. Is there any method to derive such an XML Schema document by referring to the database and the table?
Thanks in Advance
Start Free Trial
[+][-]02.17.2008 at 06:57PM PST, ID: 20917247

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Extensible Markup Language (XML), C# Programming Language, SQL Server 2005
Tags: C#, XML, Bulk Load, XML Schema
Sign Up Now!
Solution Provided By: anarki_jimbel
Participating Experts: 1
Solution Grade: B
 
 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • Automotive
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Displays / Monitors
  • Handhelds / PDAs
  • Components
  • Peripherals
  • Laptops/Notebooks
  • Servers
  • Misc
  • Apple
  • Embedded Hardware
  • Networking Hardware
  • Storage
  • Desktops
  • New Users
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMware
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Virtualization
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • Web Computing
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Consulting
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMware
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Automation
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Web Services
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Web Computing
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Lounge
  • Business Travel
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
  • Automotive
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
02.17.2008 at 06:57PM PST, ID: 20917247
anarki_jimbel:
There is simple code to get schema - see the snippet. All you need to do is to put your xml file into a bin folder. Schema file will appear there after you click the button.

The task might be a bit mote tricky. You might want to set ds table column types to some specific data types.
So see the second snippet
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

        private void button1_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            ds.ReadXml("MyXml.xml");
            ds.WriteXmlSchema("MyXmlSchema.xls");
        }
 
 
============================
 
        private void button1_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(new DataTable("MyTable"));
            ds.Tables["MyTable"].Columns.Add(new DataColumn("col1", typeof(int)));
            ds.Tables["MyTable"].Columns.Add(new DataColumn("col2", typeof(DateTime)));
            ds.Tables["MyTable"].Columns.Add(new DataColumn("col3", typeof(string)));
            ds.Tables["MyTable"].Columns.Add(new DataColumn("col4", typeof(double)));
 
            ds.Tables["MyTable"].ReadXml("MyXml.xml");
            ds.WriteXmlSchema("MyXmlSchema.xls");
        }
Open in New Window
Accepted Solution
 
 
20080716-EE-VQP-32 / EE_QW_2_20070628