Handling Simple XML Using T-SQL

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/eleiba

  • The obvious next step should have been to split the NAME and VALUE data into two columns.  The table presented in the article would be difficult to query or generate meaningful results from.

    So long, and thanks for all the fish,

    Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

  • The biggest concerns with this approach is how to deal with the big XML documents. Obviousely, it is limited to 8000 chars.

    David Zeng

    Kindest Regards,

    David Zeng

  • You are not limited to varchar/char datatypes.  This is a limitation of the example because it is using a local variable.  You are permitted to use a text datatype if you wish.  However, you need to be aware that the performance of using OPENXML versus using data from a table can be significantly slower. 

    Have a look at topic "OPENXML" in BOL.  It has further examples which include how to show the data as separate columns.

    One of these examples is :

    declare @idoc int

    declare @doc varchar(1000)

    set @doc ='


    <Customer CustomerID="VINET" ContactName="Paul Henriot">

       <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"


          <OrderDetail ProductID="11" Quantity="12"/>

          <OrderDetail ProductID="42" Quantity="10"/>



    <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">

       <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"


          <OrderDetail ProductID="72" Quantity="3"/>




    --Create an internal representation of the XML document.

    exec sp_xml_preparedocument @idoc OUTPUT, @doc

    -- SELECT stmt using OPENXML rowset provider

    SELECT *

    FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)

             WITH (OrderID       int         '../@OrderID',

                   CustomerID  varchar(10) '../@CustomerID',

                   OrderDate   datetime    '../@OrderDate',

                   ProdID      int         '@ProductID',

                   Qty         int         '@Quantity')


    This is the result:

    OrderID CustomerID           OrderDate                 ProdID    Qty


    10248      VINET       1996-07-04 00:00:00.000   11      12

    10248      VINET       1996-07-04 00:00:00.000   42      10

    10283      LILAS       1996-08-16 00:00:00.000   72      3


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

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