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,
SherylHi 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/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.)|||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/e...ft.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 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 vs xquery behind the scenes ?

Also, with regards the changes from Sql 2000 to Sql 2005.

When dealing with parsing/querying xml within a stored procedure, what is the difference (internally to sql server) between using OPENXML and xquery statements within a stored procedure ? With openxml, you need to call sp_xml_preparedocument, which will load a special version of the msxml parser called msxmlsql and loads the entire document into memory.

One thing that I like about XQUERY is that you can use the CROSS APPLY join to process database XML data on a row by row basis. I am new to using XML so I am also interested in hering the answer.