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
>

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,
KISHORIf 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@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
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:

> If what you want is the <Customer> element with all attributes, then you c
an
> 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 nod
e
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at lea
st
> I want some round about way, which will solve my problem. I am here pastin
g
> 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
>
>
>

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,
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@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
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:

> 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@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> 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
>
>
>