OPENXML problem with xmlns:xsd

The following works fine and gives expected result:
DECLARE @.doc varchar(8000)
SET @.doc ='
<ScheduleDefinition>
<WlyRecurrence >
<WsInterval>1</WsInterval>
<DaysOfW>
<Tuesday>true</Tuesday>
</DaysOfW>
</WlyRecurrence>
</ScheduleDefinition>
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
SELECT * FROM
OPENXML (@.idoc, '/ScheduleDefinition/WlyRecurrence', 2) WITH
(WsInterval int, DaysOfW xml)
--query result
WsInterval DaysOfW
1 <DaysOfW><Tuesday>true</Tuesday></DaysOfW>
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>
<WlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
2006/03/15/reporting/reportingservices">
<WsInterval>1</WsInterval>
<DaysOfW>
<Tuesday>true</Tuesday>
</DaysOfW>
</WlyRecurrence>
</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 WlyRecurrence element to make it work.
before: <WlyRecurrence xmlns="http://schemas.microsoft.com/
sqlserver/2006/03/15/reporting/reportingservices">
after: <WlyRecurrence >
However, I can't remove the xml namespace from the WlyRecurrence
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>
> <WlyRecurrence >
> <WsInterval>1</WsInterval>
> <DaysOfW>
> <Tuesday>true</Tuesday>
> </DaysOfW>
> </WlyRecurrence>
> </ScheduleDefinition>
> '
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;
> SELECT * FROM
> OPENXML (@.idoc, '/ScheduleDefinition/WlyRecurrence', 2) WITH
> (WsInterval int, DaysOfW xml)
> --query result
> WsInterval DaysOfW
> 1 <DaysOfW><Tuesday>true</Tuesday></DaysOfW>
> 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>
> <WlyRecurrence xmlns="http://schemas.microsoft.com/sqlserver/
> 2006/03/15/reporting/reportingservices">
> <WsInterval>1</WsInterval>
> <DaysOfW>
> <Tuesday>true</Tuesday>
> </DaysOfW>
> </WlyRecurrence>
> </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 WlyRecurrence element to make it work.
> before: <WlyRecurrence xmlns="http://schemas.microsoft.com/
> sqlserver/2006/03/15/reporting/reportingservices">
> after: <WlyRecurrence >
> However, I can't remove the xml namespace from the WlyRecurrence
> 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:
>