OPENXML problem with xmlns:xsd

The following works fine and gives expected result:
DECLARE @.doc varchar(8000)
SET @.doc ='
<ScheduleDefinition>
<WeeklyRecurrence >
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Tuesday>true</Tuesday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
SELECT * FROM
OPENXML (@.idoc, '/ScheduleDefinition/WeeklyRecurrence', 2) WITH
(WeeksInterval int, DaysOfWeek xml)
--query result
WeeksIntervalDaysOfWeek
1<DaysOfWeek><Tuesday>true</Tuesday></DaysOfWeek>
But, when @.doc is changed (just added xmlns to the nodes) as follows:
<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">2006-08-14T03:25:00.000-05:00</
StartDateTime>
<WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Tuesday>true</Tuesday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>
the query doesn't work. How can I get the same results? What am I
missing? Thanks in advance.
Changing the call to the stored proc sp_xml_preparedocument (by adding
the xpath_namespaces) doesn't work either:
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<ScheduleDefinition
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://
www.w3.org/2001/XMLSchema-instance" />';
In addition to the above change, I need to delete the xmlns attribute
from the WeeklyRecurrence element to make it work.
before: <WeeklyRecurrence xmlns="http://schemas.microsoft.com/
sqlserver/2006/03/15/reporting/reportingservices">
after: <WeeklyRecurrence >
However, I can't remove the xml namespace from the WeeklyRecurrence
element. Given that, how do I get the results I mentioned in the
previous post?
Thanks.
On Dec 6, 4:17 pm, preddy <prvang...@.gmail.com> wrote:
> The following works fine and gives expected result:
> DECLARE @.doc varchar(8000)
> SET @.doc ='
> <ScheduleDefinition>
> <WeeklyRecurrence >
> <WeeksInterval>1</WeeksInterval>
> <DaysOfWeek>
> <Tuesday>true</Tuesday>
> </DaysOfWeek>
> </WeeklyRecurrence>
> </ScheduleDefinition>
> '
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
> SELECT * FROM
> OPENXML (@.idoc, '/ScheduleDefinition/WeeklyRecurrence', 2) WITH
> (WeeksInterval int, DaysOfWeek xml)
> --query result
> WeeksInterval DaysOfWeek
> 1 <DaysOfWeek><Tuesday>true</Tuesday></DaysOfWeek>
> But, when @.doc is changed (just added xmlns to the nodes) as follows:
> <ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">2006-08-14T03:25:00.000-05:00</
> StartDateTime>
> <WeeklyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">
> <WeeksInterval>1</WeeksInterval>
> <DaysOfWeek>
> <Tuesday>true</Tuesday>
> </DaysOfWeek>
> </WeeklyRecurrence>
> </ScheduleDefinition>
> the query doesn't work. How can I get the same results? What am I
> missing? Thanks in advance.
|||Look in BOL for WITH XMLNAMESPACES, you have a default namespace on the
data.
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
"preddy" <prvangala@.gmail.com> wrote in message
news:bb4e9b29-6b0a-433c-802a-f63d64a7ad74@.l1g2000hsa.googlegroups.com...
> Changing the call to the stored proc sp_xml_preparedocument (by adding
> the xpath_namespaces) doesn't work either:
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc, '<ScheduleDefinition
> xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://
> www.w3.org/2001/XMLSchema-instance" />';
> In addition to the above change, I need to delete the xmlns attribute
> from the WeeklyRecurrence element to make it work.
> before: <WeeklyRecurrence xmlns="http://schemas.microsoft.com/
> sqlserver/2006/03/15/reporting/reportingservices">
> after: <WeeklyRecurrence >
> However, I can't remove the xml namespace from the WeeklyRecurrence
> element. Given that, how do I get the results I mentioned in the
> previous post?
> Thanks.
>
> On Dec 6, 4:17 pm, preddy <prvang...@.gmail.com> wrote:
>