How Can I read this XML

Hi,
I want to use SQL Server to read this XML and copy data in to DB. How Can I
do this .. I know how to use OPENXML,however in this XML I don't khow how to
use mapping ...
Here is my XML
- <ROOT>
<Field name="CustNo" logicalFld="CustNo_logical" value="123333333555" />
<Field name="Name" logicalFld="Name1" value="Tom Cruse" />
<Field name="phone" logicalFld="phone1" value="(xxx)123-4567" />
</ROOT>
I want to genrate table like this ..
Fieldname LogicalFldValue
CustnoCustNo_logical123333333555
NameName1Tom Cruse
phonephone1(xxx)123-4567
any other suggestion ...
Thanks
-Kris
Did you look at SQL Books Online for OPENXML?
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')
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:9A68E8C6-0AF3-4773-B00B-F12D78F19AF9@.microsoft.com...
> Hi,
> I want to use SQL Server to read this XML and copy data in to DB. How Can
I
> do this .. I know how to use OPENXML,however in this XML I don't khow how
to
> use mapping ...
> Here is my XML
> - <ROOT>
> <Field name="CustNo" logicalFld="CustNo_logical" value="123333333555" />
> <Field name="Name" logicalFld="Name1" value="Tom Cruse" />
> <Field name="phone" logicalFld="phone1" value="(xxx)123-4567" />
> </ROOT>
> I want to genrate table like this ..
> Fieldname LogicalFld Value
> Custno CustNo_logical 123333333555
> Name Name1 Tom Cruse
> phone phone1 (xxx)123-4567
> any other suggestion ...
> Thanks
> -Kris