MS SQL XML - OPENXML with a namespace

Author

OPENXML with a namespace

2006-02-05, 8:23 pm

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_preparedocume
nt @idoc OUTPUT, @FileContents, '<Members
xmlns="http://tempuri.org/template.xsd"/>'

SELECT *
FROM OPENXML(@idoc, '/Members/Member',2)
WITH (OperationType varchar(30))

EXEC sp_xml_removedocumen
t @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

Steven Cheng[MSFT]

2006-02-06, 3:23 am

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_prparedocumen
t 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_preparedocum
ent
http://msdn.microsoft.com/library/e...o.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.)

2006-02-06, 8:25 pm

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_prparedocumen
t 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_preparedocum
ent
> http://msdn.microsoft.com/library/e...o.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.)
>
>
>

Steven Cheng[MSFT]

2006-02-06, 8:25 pm

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.)

Sponsored Links