Query regarding OPENXML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query regarding OPENXML

Author

Topic

ravinder1483
Starting Member

9 Posts

Posted - 2011-02-04 : 10:40:33

Hi,

I have a query regarding OPENXML. I have seen many examples where in people use ‘@’ in WITH clause of OPENXML.
For example in the following example, we can see @OrderID, @CustomerID, @OrderDate, @ProductID, @Quantity.

I am very confused about what this does???

Please let me know as i am really getting mad about finding the use of ‘@’.

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')

Ravinder Pal Singh
Email: ravinder1483@gmail.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-04 : 13:07:47

First, don't use OPENXML. Start to learn to use XQuery.
The use of "@" is to point to an attribute in favor of an element.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources