Reduce Database RoundTrips Using XML

  • Comments posted to this topic are about the content posted at

  • Grate article. I find it very helpful


  • I use this a lot and it is a huge performance booster. Is there any reason that you shouldn't use the text or ntext data type as input to the stored procedure to avoide the 4000 byte limit? That would allow you 2,147,483,647 and 1,073,741,823 bytes respectively. I can't see passing that much data being practical, but you can blow out 4000 bytes quickly in an xml document. m I missing something here?


    sp_xml_preparedocument hdoc OUTPUT

    [, xmltext]

    [, xpath_namespaces]



    Is the handle to the newly created document. hdoc is an integer.


    Is the original XML document. The MSXML parser parses this XML document.  xmltext is a text (char, nchar, varchar, nvarchar, text, or ntext) parameter. The default value is NULL, in which case an internal representation of an empty XML document is created.


    Specifies the namespace declarations that are used in row and column XPath expressions in OPENXML. The default value is <root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop">.

    xpath_namespaces provides the namespace URIs for the prefixes used in the XPath expressions in OPENXML by means of a well-formed XML document. xpath_namespaces declares the prefix that must be used to refer to the namespace urn:schemas-microsoft-com:xml-metaprop, which provides meta data about the parsed XML elements. Although you can redefine the namespace prefix for the metaproperty namespace using this technique, this namespace is not lost. The prefix mp is still valid for urn:schemas-microsoft-com:xml-metaprop even if xpath_namespaces contains no such declaration. xpath_namespaces is a text (char, nchar, varchar, nvarchar, text, or ntext) parameter.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply