HELP ON XML

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,
KISHORHello,
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 jus
t
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
"Razvan Socol" wrote:

> 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
>|||> here you are concating ContactName...
> but I dont want to have a hardcoding like this.
You already did hardcoding: in the parameters of the OPENXML function,
in the WITH clause.

> I have tried this also [...] for xml auto [...] But gave me error
[...]
Try this:
[...]
SELECT * INTO #tmp
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
SELECT * FROM #tmp FOR XML AUTO
DROP TABLE #tmp
Razvan|||Yes,
Just to explain you all I have done .. I just want inner attributes...
if you know .. let me know.
TIA
Kishor
"Razvan Socol" wrote:

> You already did hardcoding: in the parameters of the OPENXML function,
> in the WITH clause.
>
> [...]
> Try this:
> [...]
> SELECT * INTO #tmp
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> SELECT * FROM #tmp FOR XML AUTO
> DROP TABLE #tmp
> Razvan
>