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
>
>
>