Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

This Query will give me result

CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez

This is fine but I want to get results like this.

COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’

Please suggest me some ways to achieve this

TIA,
KISHOR

If what you want is the <Customer> element with all attributes, then you can
use this code:

SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@mp:xmltext')

This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>

On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@mp:localname',
CustomerID varchar(10),
ContactName varchar(20))

This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"

(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)

Cheers,
Graeme

--
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com


"kishor" <kishor (AT) discussions (DOT) microsoft.com> wrote

Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

This Query will give me result

CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez

This is fine but I want to get results like this.

COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'

Please suggest me some ways to achieve this

TIA,
KISHOR

Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@mp:xmltext'

Regards,
Kishor.

"Graeme Malcolm" wrote: