RE: Stand alone Shredding in SQL Server 2005

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

Relevant Pages

  • Stand alone Shredding in SQL Server 2005
    ... what I've read it will have to be some type of bulk processing. ... from the command line or bulk insert from T-SQL. ... the xml I want to shred: ...
    (microsoft.public.sqlserver.xml)
  • Re: Importing XML data into Access and exporting Access table to XML form
    ... also Application.ExportXML, which can export a table or query to an XML ... script transform the XML file into a CSV file, ... >XML file to be loaded into a Access table from command line of a VB ...
    (microsoft.public.access.externaldata)
  • Importing xml file into simmechanics
    ... Im have been using the simmechanics link software to create an xml of my solidworks model which was sucessful after about a week of trying. ... i have the 2008a version of matlab and have been attempting to use import_physmod command to import the xml file. ...
    (comp.soft-sys.matlab)
  • Re: XML and DTS
    ... allow you to do this in DTS, or you can use SQLXML which has a bulk insert ... > Can I specify a XML file as a Source file in a DTS package? ...
    (microsoft.public.sqlserver.dts)
  • Re: Commandline XML parser
    ... >> several XML file. ... XMLStarlet: ... Command line XML with XMLStarlet ...
    (comp.text.xml)
Quantcast