OPENXML with Multiple Namespace

Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
hi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
*** Sent via Developersdex http://www.codecomments.com ***
|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCdResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1data2
(0 row(s) affected)
Thanks
Sai
"Chandra" wrote:

> hi Sai
> what is the message that you see.
> best Regards,
> Chandra
> http://www.SQLResource.com/
> http://chanduas.blogspot.com/
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||It's because ResultData is now in the tempuri namespace and you've added a
node to the path that is now missing in your OPENXML statement. Change your
code to:
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:t="http://tempuri.org/" />'
SELECT * FROM OPENXML(@.idoc,
'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
WITH
(
dat1 varchar(255) 't:dat1',
data2 varchar(255) 't:data2'
)
EXEC sp_xml_removedocument @.idoc
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Sai" <Sai@.discussions.microsoft.com> wrote in message
news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
Hi
I would appriciate if any one could help me the following situation.
The following code is working fine
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
if I add another tag
<Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
after adding the above the code looks as follows
-----
DECLARE @.doc varchar(2000), @.hDoc int
DECLARE @.idoc int
SET @.doc =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<Websvc xmlns="http://tempuri.org/">
<ResultData>
<dat1>ResultCd</dat1>
<data2>ResultMessage</data2>
</ResultData>
</Websvc>
</soap:Body>
</soap:Envelope>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
WITH
(
dat1 varchar(255),
data2 varchar(255)
)
EXEC sp_xml_removedocument @.idoc
-----
Thanks
Sai
|||Graeme
Thanks a lot,that was a great solution.
I was trying for it for long time
I greately appriciate your time and help.
Sai
"Graeme Malcolm" wrote:

> It's because ResultData is now in the tempuri namespace and you've added a
> node to the path that is now missing in your OPENXML statement. Change your
> code to:
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:t="http://tempuri.org/" />'
> SELECT * FROM OPENXML(@.idoc,
> 'soap:Envelope/soap:Body/t:Websvc/t:ResultData',2)
> WITH
> (
> dat1 varchar(255) 't:dat1',
> data2 varchar(255) 't:data2'
> )
> EXEC sp_xml_removedocument @.idoc
> Cheers,
> Graeme
>
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "Sai" <Sai@.discussions.microsoft.com> wrote in message
> news:BB058093-023F-4CDA-B19C-31ADC9C7AF18@.microsoft.com...
> Hi
> I would appriciate if any one could help me the following situation.
> The following code is working fine
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> if I add another tag
> <Websvc xmlns="http://tempuri.org/"> after <soap:Body> it is not working
> after adding the above the code looks as follows
> -----
> DECLARE @.doc varchar(2000), @.hDoc int
> DECLARE @.idoc int
> SET @.doc =
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
> <soap:Body>
> <Websvc xmlns="http://tempuri.org/">
> <ResultData>
> <dat1>ResultCd</dat1>
> <data2>ResultMessage</data2>
> </ResultData>
> </Websvc>
> </soap:Body>
> </soap:Envelope>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc,'<soap:Envelope
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" />'
> SELECT * FROM OPENXML(@.idoc, 'soap:Envelope/soap:Body/ResultData',2)
> WITH
> (
> dat1 varchar(255),
> data2 varchar(255)
> )
> EXEC sp_xml_removedocument @.idoc
> -----
> Thanks
> Sai
>
>