Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Handling Simple XML Using T-SQL Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2004 6:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 2:05 AM
Points: 47, Visits: 18
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/eleiba
Post #133684
Posted Monday, September 20, 2004 10:15 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:43 AM
Points: 753, Visits: 127
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

Post #137848
Posted Monday, September 20, 2004 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 15, 2007 7:03 AM
Points: 142, Visits: 1

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
MCDBA
Post #137850
Posted Monday, September 20, 2004 6:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 2,967, Visits: 2,569

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 ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
           OrderDate="1996-07-04T00:00:00">
      <OrderDetail ProductID="11" Quantity="12"/>
      <OrderDetail ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
           OrderDate="1996-08-16T00:00:00">
      <OrderDetail ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--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

 




Post #137895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse