sql server programming: 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

Hello,

Try this (obvious) query:

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 'Customer CustomerID="'+CustomerID
+'" ContactName="'+ContactName+'"' AS COLONE
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))

Is this what you need ?

Razvan

Hi Razvan,
Thanxs But this will not work. what I actually want is to get all inner
attribute of a xml. here you are concating ContactName...but I dont want to
have a hardcoding like this. client can pass Name , Cname...any thing. I just
want a list of all all attribute.


I have tried this also

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 CustomerID ,ContactName
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)
)
for xml auto


But gave me error

Unnamed column or table names cannot be used as XML identifiers. Name
unnamed columns using AS in the SELECT statement.


Regards,
Kishor


[quoted text, click to view]