OPENXML with a namespace

Sorry, I've tried to search to find my answer, but I just can't quite figure
it out... I'm new to querying XML in a stored proc...
I have an XML file that looks like:
<Members xmlns="http://tempuri.org/template.xsd">
<Member>
<OperationType>Insert</OperationType>
</Member>
<Member>
<OperationType>Update</OperationType>
</Member>
</Members>
there's more to it, but I've simplified it down.
and in my stored proc, I'm trying this: (assume that @.idoc is already
declared and @.FileContents contains the contents of the xml file I'm
reading)
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.FileContents, '<Members
xmlns="http://tempuri.org/template.xsd"/>'
SELECT *
FROM OPENXML(@.idoc, '/Members/Member',2)
WITH (OperationType varchar(30))
EXEC sp_xml_removedocument @.idoc
I get no results...
If I take the xmlns declaration out of the <Members> element in the source
file, it works fine and I get a list of the OperationType elements.
What am I missing?
Thanks much,
Sheryl
Hi SL,
Welcome to the MSDN newsgroup.
Regarding on the OPENXML with namespace problem, based on my research, we
can use the third parameter of the sp_xml_prparedocument procedure to
specify namespace info. Also, in our sequental xpath, we need to explicitly
identify namespace through a namespace prefix. For detailed info, you can
refer to the following MSDN document and web article:
#How To: Use OpenXML on an Xml document with a default namespace
http://www.sqlxml.org/faqs.aspx?faq=101
#sp_xml_preparedocument
http://msdn.microsoft.com/library/en...asp?frame=tru
e
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||That was perfect, thanks!
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:GubgkovKGHA.3680@.TK2MSFTNGXA02.phx.gbl...
> Hi SL,
> Welcome to the MSDN newsgroup.
> Regarding on the OPENXML with namespace problem, based on my research, we
> can use the third parameter of the sp_xml_prparedocument procedure to
> specify namespace info. Also, in our sequental xpath, we need to
> explicitly
> identify namespace through a namespace prefix. For detailed info, you can
> refer to the following MSDN document and web article:
>
> #How To: Use OpenXML on an Xml document with a default namespace
> http://www.sqlxml.org/faqs.aspx?faq=101
> #sp_xml_preparedocument
> http://msdn.microsoft.com/library/en...asp?frame=tru
> e
> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||You're welcome SL,
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

OPENXML question

Can't get the following code to return two rows:
DECLARE @.data varchar(1000)
DECLARE @.hdoc int
select @.data = '<root><guid>a1</guid><guid>b1</guid></root>'
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.data
select *
from openxml(@.hdoc, '/root', 2)
with (guid varchar(10))
EXEC sp_xml_removedocument @.hdoc
Because you have only one root element you will only get one row.
Use
select *
from openxml(@.hdoc, '/root/guid', 2)
with (guid varchar(10) '.')
HTH
Michael
"Mark Frishman" <mfrishman_nospam@.hotmail.com> wrote in message
news:%23Bcjk7lfEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Can't get the following code to return two rows:
> DECLARE @.data varchar(1000)
> DECLARE @.hdoc int
> select @.data = '<root><guid>a1</guid><guid>b1</guid></root>'
> EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.data
> select *
> from openxml(@.hdoc, '/root', 2)
> with (guid varchar(10))
> EXEC sp_xml_removedocument @.hdoc
>