Stand alone Shredding in SQL Server 2005
- From: "RobMaryland" <RobMaryland@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 27 Sep 2005 15:07:03 -0700
I've been going around in circles trying to find the simplest way to shred a
large number of documents into a single table using SS 2005. I'm
overwhelmed by the volume of info and methods available. Performance is the
biggest consideration, as the volume is expected to be large in prod, so from
what I've read it will have to be some type of bulk processing.
My first cut:
I created an xsd, but what command do I use to process the xml file and
shred the data into the corresponding columns in the target table? I don't
want to use VB script if I can help it, and would prefer something like bcp
from the command line or bulk insert from T-SQL.
Here is my test table:
CREATE TABLE [dbo].[Employees](
[LastName] [char](10),
[FirstName] [char](10) ,
[EmployeeID] [int] NULL)
and the XSD that I created:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Employee" sql:relation="Employees" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FName"
sql:field="FirstName"
type="xsd:string" />
<xsd:element name="LName"
sql:field="LastName"
type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="EmpID"
sql:field="EmployeeID"
type="xsd:integer" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
And finally, the xml I want to shred:
<Employee>
<FName> Robby </FName>
<LName> Hall </LName>
<EmpID> 123 </EMPID>
</Employee>
<Employee>
<FName> Eddy </FName>
<LName> obbie </LName>
<EmpID> 456 </EMPID>
</Employee>
<Employee>
<FName> Johnny </FName>
<LName> Reb </LName>
<EmpID> 789 </EMPID>
</Employee>
So in my perfect world, I would like to issue a TSQL command like:
Bulk Insert into dbo.Employees using Employee.xsd with input file
c:\employee.xml
and the table would then magically contain:
Last Name first name Emp#
--------------------------------------
Hall Robby 123
Obbie Eddie 456
Reb Johnny 789
Is something similar to this possible? Or is a lot more work required,
such as coding xpath and xquery statements to pull the elements I want from
the XML? I'm looking for performance and ease of maintenance, as I expect
new elements will be added quarterly.
Thanks!!!
.
- Prev by Date: Re: Import XML file into Multiple SQL Tables
- Next by Date: How do I insert text into empty xml element in sql server 2005?
- Previous by thread: Import XML file into Multiple SQL Tables
- Next by thread: How do I insert text into empty xml element in sql server 2005?
- Index(es):
Relevant Pages
|
