MS SQL XML - Stand alone Shredding in SQL Server 2005

Author

Stand alone Shredding in SQL Server 2005

RobMaryland

2005-09-27, 8:24 pm

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

Jinghao Liu - MS SQL XML Test

2005-09-30, 8:24 pm

SQLXML is the technology you want to use. You can see detail of it at
http://msdn.microsoft.com/library/d...nch_sqlxml.asp.

Basically it allows you using XSD to describe a set of relation tables. So
those relational data will be viewed as XML data. You can use xpath to query
them. When you load xml into it, it will be shreded into tables.

"RobMaryland" wrote:

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

James

2005-10-31, 11:23 am

Try XMLengine. http://www.talinum.com/products.htm

James

"RobMaryland" wrote:

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

Sponsored Links