Handling Simple XML Using T-SQL

Question

Handling Simple XML Using T-SQL

Eli Leiba

SSC-Addicted

Points: 461
More actions
August 24, 2004 at 6:28 pm

#162140

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

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

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

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 1

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

David Zeng SSC Veteran Points: 234 More actions · Answer 2

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

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>

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

OrderDate="1996-07-04T00:00:00">

</Order>

</Customer>

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

OrderDate="1996-08-16T00:00:00">

</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