Re: openxml question


"JXStern" <JXSternChangeX2R@xxxxxxx> wrote in message
news:m5t5o354tgl5ko0tki5dpr8spkh7s0h6a8@xxxxxxxxxx
On Mon, 7 Jan 2008 21:32:31 -0500, "Mike C#" <xyz@xxxxxxx> wrote:

Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type
variable
or column? I ask because casting character/binary data to XML and
shredding
it to relational format are two completely different tasks.

Cast it to XML variable with schema to validate via XSD.

Then pass it to SP as XML with no schema, shred it there with four to
six queries mostly cross applies.

Only reason I use the unschema'd XML in the SP is so I can change the
XSD without first dropping the SP. I timed it both ways, seems to
make very little difference. The shredding is indecently fast. Takes
two seconds to shred into table vars, about ten seconds to write rows
to database.

openrowset used like this doesn't do that 1/8 of RAM, does it?

No, I'm not aware of openrowset using 1/8 RAM. The documented reason for
openxml using 1/8 RAM is MSXML. Since openrowset isn't actually parsing the
XML data (it's just loading the file into memory as a blob), it doesn't
invoke MSXML.

I noticed the XML schema collection doesn't seem to make a large difference
for basic shredding either, although it might help improve performance for
more complex querying and shredding operations with lots of calculations and
data type casting.

Indexing with at least a primary XML index pre-shreds the data, and provides
a significant performance improvement in shredding--but of course you can't
use an XML index on a variable. The secondary XML index doesn't seem to
make as much difference for simple shreds of fairly simple XML documents
though. Secondary XML indexes may improve performance for complex documents
and complex shredding operations, but I haven't tested it with any
complicated paths or XML documents yet.


.

Relevant Pages

  • Re: SSIS 2005 - lading multiple XML files
    ... Do you want to insert the document en masse into your XML column or do you ... want to do shredding of the XML data within the SSIS pipeline? ...
    (microsoft.public.sqlserver.dts)
  • Re: openxml question
    ... shredding an XML type column with nodesmethod and primary XML index ... OPENXML appears to be slightly faster for simple XML documents, ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml question
    ... The secondary XML index doesn't seem to ... Secondary XML indexes may improve performance for complex ... Anyway, when I'm shredding a 2mb, 2000 row document in two seconds, how ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml question
    ... I ask because casting character/binary data to XML and shredding ... Cast it to XML variable with schema to validate via XSD. ... The shredding is indecently fast. ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml question
    ... XML datatype for shredding. ... Are you actually shredding the XML data after you load it using ... openrowsetor are you actually just storing it an an XML type variable ...
    (microsoft.public.sqlserver.xml)
Loading