OpenXML with namespace and no prefix

I'm trying to run a query on some xml using openxml. The xml has a
namespace withough a prefix, and I'm having trouble. Below is a sample of
the xml and my openxml query. It runs but doesn't give back the expected
resultset. Any hints?
SELECT @.strIDXml = '<?xml version="1.0"?><IDs
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="http://www.interiorhealth.ca/DevSupport"><ID>3</ID><ID>2</ID></IDs>'
DECLARE @.idoc int
EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
xmlns="http://www.interiorhealth.ca/DevSupport"/>'
SELECT
2,[ID]
FROM
OpenXML(@.idoc, '/IDs/ID')
WITH (
[ID] int '.'
)
EXECUTE sp_xml_removedocument @.iDocJeremy wrote:
> I'm trying to run a query on some xml using openxml. The xml has a
> namespace withough a prefix, and I'm having trouble. Below is a sample of
> the xml and my openxml query. It runs but doesn't give back the expected
> resultset. Any hints?
For your query you need to bind a prefix to the default namespace and
use that prefix as in the following example:
EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
xmlns:pf="http://www.interiorhealth.ca/DevSupport"/>'
SELECT
2,[ID]
FROM
OpenXML(@.idoc, '/pf:IDs/pf:ID')
WITH (
[ID] int '.'
)
You are free to choose any valid prefix you like, just make sure it is
bound to the namespace URI defined in the original XML.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||thanks!
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ux0UArHxHHA.736@.TK2MSFTNGP06.phx.gbl...
> Jeremy wrote:
> For your query you need to bind a prefix to the default namespace and use
> that prefix as in the following example:
> EXECUTE sp_xml_preparedocument @.idoc OUTPUT, @.strIDXml, '<IDs
> xmlns:pf="http://www.interiorhealth.ca/DevSupport"/>'
> SELECT
> 2,[ID]
> FROM
> OpenXML(@.idoc, '/pf:IDs/pf:ID')
> WITH (
> [ID] int '.'
> )
> You are free to choose any valid prefix you like, just make sure it is
> bound to the namespace URI defined in the original XML.
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/