Re: openxml seemingly fails to process external entity

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks for the definitive answer, even if it's not exactly the one I'd hoped
for.

"Michael Rys [MSFT]" wrote:

> External entities are not supported with OpenXML for a variety of security
> related reasons.
>
> Best is to resolve them on the client/midtier side before sending the XML to
> the server.
>
> Best regards
> Michael
>
> "HolmesDM" <HolmesDM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:8AF86E16-03D4-49A7-9A20-8133C2B0E109@xxxxxxxxxxxxxxxx
> >I am having a problem getting OPENXML to process some xml that contains an
> > "external entity" in SQL Server 2000.
> >
> > Background, which you probably already know:
> > ----------
> > Xml uses "external entities" the way the C language uses "#include": when
> > an xml processor finds the directive, it refers to an external file whose
> > contents are to be processed at that point.
> >
> >
> > I have created a minimal example that illustrates the problem.
> >
> > 1. First, here is some SQL that correctly processes some XML that does
> > *not* use external entities. The XML is stored in a variable (@testXml),
> > and
> > a select statement reads this XML and reports some basic facts about the
> > only
> > data in it, namely, that there is a tag called "DatastreamDownload ":
> >
> > -- demonstrate validity of simple xml
> > declare @testXml varchar(8000)
> > set @testXml ='
> > <?xml version="1.0" ?>
> > <DatastreamDownload />
> > '
> >
> > declare @idoc int
> > exec sp_xml_preparedocument @idoc output, @testXml
> >
> > select *
> > from openxml( @idoc, '/DatastreamDownload', 0)
> >
> > exec sp_xml_removedocument @idoc
> >
> >
> > Here are the beginnings of the lines I see in SQL Query Analyzer:
> > id parentid nodetype localname
> > -------------------- -------------------- ----------- -------------------
> > 0 NULL 1
> > DatastreamDownload
> >
> > (1 row(s) affected)
> >
> >
> > 2. I then stored the XML from @testXml in a file, simple.xml. The
> > following 2 lines show the contents of the file, except that I've added 4
> > spaces to the beginning of each of the lines in this listing (that is, the
> > prefixed spaces are not in the file):
> > <?xml version="1.0" ?>
> > <DatastreamDownload />
> >
> >
> > 3. I created a second file of XML, simpleEntity.xml, that includes
> > simple.xml by using a external entity. The following 11 lines show the
> > contents of the file, except that I've added 4 spaces to the beginning of
> > each of the lines in this listing (that is, the prefixed spaces are not in
> > the file):
> > <?xml version="1.0" ?>
> > <!DOCTYPE just4ExternalEntity
> > [
> > <!ENTITY externalEntity SYSTEM "simple.xml">
> > ]
> > >
> >
> > <just4ExternalEntity>
> > <workingTag />
> > &externalEntity;
> > </just4ExternalEntity>
> > You can verify that this external entity stuff is done properly by opening
> > this second file in Internet Explorer. I see something like this, except
> > with better colors:
> > <?xml version="1.0" ?>
> > <!DOCTYPE just4ExternalEntity (View Source for full doctype...)>
> > - <just4ExternalEntity>
> > <workingTag />
> > <DatastreamDownload />
> > </just4ExternalEntity>
> >
> > 4. Finally, I copied the working xml from simpleEntity.xml into a SQL
> > variable, @xmlDoc. I had to prefix a network path to "simple.xml", to
> > give
> > the SQL Server a chance of locating simple.xml. I've represented this
> > network path by "\\unc..." below, although in my actual test I used a
> > valid
> > (and triple-tested) unc.
> >
> > Openxml can partially process this code. The first select statement below
> > reports on the "workingTag" tag, which is defined in the @xmlDoc variable
> > ,
> > but fails to report on the sibling "DatastreamDownload" tag, which is
> > defined
> > in the external entity.
> >
> > -- attempt to read the same xml from a disk file using an external
> > entity
> > declare @xmlDoc varchar(1000)
> > set @xmlDoc = -- code from simpleEntity.xml
> > '
> > <?xml version="1.0" ?>
> > <!DOCTYPE just4ExternalEntity
> > [
> > <!ENTITY externalEntity SYSTEM "\\unc...\simple.xml">
> > ]
> > >
> >
> > <just4ExternalEntity>
> > <workingTag />
> > &externalEntity;
> > </just4ExternalEntity>
> > '
> > declare @jdoc int
> > declare @rc int
> > exec @rc = sp_xml_preparedocument @jdoc output, @xmlDoc
> >
> > select @rc as resultCode
> >
> > -- lists row, as expected
> > select *
> > from openxml( @jdoc, '/just4ExternalEntity/workingTag', 0)
> >
> > -- fails to list row
> > select *
> > from openxml( @jdoc, '/just4ExternalEntity/DatastreamDownload',
> > 0)
> >
> > exec sp_xml_removedocument @jdoc
> >
> >
> >
> > Here are the beginnings of the lines I see in SQL Query Analyzer:
> > resultCode
> > -----------
> > 0
> >
> > (1 row(s) affected)
> >
> > id parentid nodetype localname
> > -------------------- -------------------- ----------- -----------
> > 7 0 1 workingTag
> >
> > (1 row(s) affected)
> >
> > id parentid nodetype localname
> > -------------------- -------------------- ----------- -----------
> >
> > (0 row(s) affected)
> >
> > I was expecting to see 1 row in the last result set above, with
> > "DatastreamDownload" in the "localname" column.
> >
> > So openxml is failing to process something that Internet Explorer has no
> > trouble with. As far as I know (and I'm no expert) external entities are
> > a
> > standard part of XML, and a standards-conforming XML processor should
> > handle
> > them. So it seems very likely that the processor used by openxml does
> > handle
> > them. It's even possible that openxml and IE are using the same
> > processor,
> > although that would quite a feat of coordination for a company the size of
> > Microsoft.
> >
> > Since external entities are standard, and since IE issues a security
> > warning
> > when I use one via a network unc, I'm suspecting that there is a privilege
> > problem in getting openxml to read the external entity.
> >
> > Thanks for any help.
>
>
>
.

Relevant Pages

  • openxml seemingly fails to process external entity
    ... an xml processor finds the directive, it refers to an external file whose ... here is some SQL that correctly processes some XML that does ... simple.xml by using a external entity. ... Openxml can partially process this code. ...
    (microsoft.public.sqlserver.xml)
  • Re: XML Buffer, OPENXML Usage Advice
    ... The memory available for OpenXML is 1/8th of available server memory (I ... >attempting to replace some looping processes with batch processes. ... but so far the XML passed into the proc has been less than 2KB ...
    (microsoft.public.sqlserver.xml)
  • can openxml write multiple fields - 1 row?
    ... quantity attributes from the XML document. ... declare @doc varchar ... FROM OPENXML ... This routine only generates one int ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml question
    ... Avoid OPENXML over large XML documents. ... Avoid large numbers of concurrent OPENXML statements over XML ...
    (microsoft.public.sqlserver.xml)
  • Re: openxml seemingly fails to process external entity
    ... External entities are not supported with OpenXML for a variety of security ... Best is to resolve them on the client/midtier side before sending the XML to ... here is some SQL that correctly processes some XML that does ...
    (microsoft.public.sqlserver.xml)