I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
Regards
Hi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards
|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.
|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan
|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan
Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts
Friday, March 9, 2012
Opinions on Option (Keepfixed Plan)
I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> > Hi Frank
> >
> > Have you read these article to determine the cause of the recompilations and
> > address the actual reasons?
> >
> > Troubleshooting stored procedure recompilation
> > http://support.microsoft.com/kb/243586
> >
> > How to identify the cause of recompilation in an SP:Recompile event
> > http://support.microsoft.com/kb/308737
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
>> Kalen Delaney wrote:
>> Hi Frank
>> Have you read these article to determine the cause of the recompilations and
>> address the actual reasons?
>> Troubleshooting stored procedure recompilation
>> http://support.microsoft.com/kb/243586
>> How to identify the cause of recompilation in an SP:Recompile event
>> http://support.microsoft.com/kb/308737
>> Yep, I did all these. I decreased SP:Recompile events in Profiler
>> substantially, however, they were still happening quite a bit.
>> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
>> (anecdotally) increased the performance of the stored proc. In Perfmon,
>> however, I still get a large number for the SQL Statistic/SQL
>> Compilations. It must mean something else than what is reflected by the
>> SP:Recompile event in Profiler.
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations and
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> > Hi Frank
> >
> > Have you read these article to determine the cause of the recompilations and
> > address the actual reasons?
> >
> > Troubleshooting stored procedure recompilation
> > http://support.microsoft.com/kb/243586
> >
> > How to identify the cause of recompilation in an SP:Recompile event
> > http://support.microsoft.com/kb/308737
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
>> Kalen Delaney wrote:
>> Hi Frank
>> Have you read these article to determine the cause of the recompilations and
>> address the actual reasons?
>> Troubleshooting stored procedure recompilation
>> http://support.microsoft.com/kb/243586
>> How to identify the cause of recompilation in an SP:Recompile event
>> http://support.microsoft.com/kb/308737
>> Yep, I did all these. I decreased SP:Recompile events in Profiler
>> substantially, however, they were still happening quite a bit.
>> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
>> (anecdotally) increased the performance of the stored proc. In Perfmon,
>> however, I still get a large number for the SQL Statistic/SQL
>> Compilations. It must mean something else than what is reflected by the
>> SP:Recompile event in Profiler.
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan
Opinions on Option (Keepfixed Plan)
I've got this situation where a certain large stored proc (that's used
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations a
nd
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan
all day long) just compiles way too often, to the point where it hurts
the performance. I've tried all the tricks I could find to reduce the
number of compilations. So my last option is to use Option (Keepfixed
Plan) hint.
Are there any downsides to using Option (Keepfixed Plan)?
RegardsHi Frank
Have you read these article to determine the cause of the recompilations and
address the actual reasons?
Troubleshooting stored procedure recompilation
http://support.microsoft.com/kb/243586
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/kb/308737
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Frank Rizzo" <none@.none.com> wrote in message
news:uuunQQy5GHA.4064@.TK2MSFTNGP03.phx.gbl...
> I've got this situation where a certain large stored proc (that's used all
> day long) just compiles way too often, to the point where it hurts the
> performance. I've tried all the tricks I could find to reduce the number
> of compilations. So my last option is to use Option (Keepfixed Plan)
> hint.
> Are there any downsides to using Option (Keepfixed Plan)?
> Regards|||Kalen Delaney wrote:
> Hi Frank
> Have you read these article to determine the cause of the recompilations a
nd
> address the actual reasons?
> Troubleshooting stored procedure recompilation
> http://support.microsoft.com/kb/243586
> How to identify the cause of recompilation in an SP:Recompile event
> http://support.microsoft.com/kb/308737
Yep, I did all these. I decreased SP:Recompile events in Profiler
substantially, however, they were still happening quite a bit.
Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
(anecdotally) increased the performance of the stored proc. In Perfmon,
however, I still get a large number for the SQL Statistic/SQL
Compilations. It must mean something else than what is reflected by the
SP:Recompile event in Profiler.|||Frank Rizzo wrote:
> Kalen Delaney wrote:
> Yep, I did all these. I decreased SP:Recompile events in Profiler
> substantially, however, they were still happening quite a bit.
> Inserting Option (Keepfixed Plan) got rid of the SP:Recompile events and
> (anecdotally) increased the performance of the stored proc. In Perfmon,
> however, I still get a large number for the SQL Statistic/SQL
> Compilations. It must mean something else than what is reflected by the
> SP:Recompile event in Profiler.
If the recompilations are caused by a large amount of
inserts/updates/deletes, then you could experiment with turning off
auto-update statistics and run a schedule to manually update the
statistics at a time that is convenient for you.
Gert-Jan|||Gert-Jan Strik wrote:
> Frank Rizzo wrote:
> If the recompilations are caused by a large amount of
> inserts/updates/deletes, then you could experiment with turning off
> auto-update statistics and run a schedule to manually update the
> statistics at a time that is convenient for you.
I turned off the auto-update stats from the get go, but the problem is
still happening.
> Gert-Jan
Saturday, February 25, 2012
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
Saihi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCd ResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1 data2
(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.examnotes.net ***
>|||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 you
r
> 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
>
>
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
Saihi Sai
what is the message that you see.
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||Chandra
Thanks for your reply
The actual result I was expecting is
dat1 data2
ResultCd ResultMessage
after I add the "<Websvc xmlns="http://tempuri.org/"> "
I am getting
dat1 data2
(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.examnotes.net ***
>|||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 you
r
> 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
>
>
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
>
>
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
>
>
Subscribe to:
Posts (Atom)