Root Namespace Prefix and OpenXml

Hi,
I can't seem to nail down the correct way to use OpenXml with input Xml that
conatains a root namespace.
'=================================
PROC
=================================
<snip>
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.ExchangeRtMgmtXml, '<root
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
INSERT INTO #ExchangeRtMgmt (
ReinscReferenceNicknameTxt,
Identifier,
ReinscReferenceNameTxt,
ReinscTypeDsc,
SourceCurrencyTypeCode,
SourceCurrencyTypeId,
TargetCurrencyTypeCode,
TargetCurrencyTypeId,
BeginDt,
Rate,
RateSource,
ParticipantIsManaged
)
SELECT ReinscReferenceNicknameTxt,
Identifier,
ReinscReferenceNameTxt,
ReinscTypeDsc,
SourceCurrencyTypeCode,
SourceCurrencyTypeId,
TargetCurrencyTypeCode,
TargetCurrencyTypeId,
BeginDt,
Rate,
RateSource,
ParticipantIsManaged
FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
WITH(
ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
Identifiernvarchar(100) '@.Identifier',
ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
BeginDtsmalldatetime '@.BeginDt',
Ratedecimal '@.Rate',
RateSourcenvarchar(1) '@.RateSource',
ParticipantIsManagedbit '@.ParticipantIsManaged'
)
EXEC sp_xml_removedocument @.iDoc
</snip>
'======================================
' Call SProc
'======================================
EXEC spr_ManagedExchangeRate_byParticipant '<?xml version="1.0"
encoding="utf-16"?><ExchangeRtParticipants
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
ReinscReferenceNicknameTxt="NACRE" Identifier="2"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
/><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
/></ExchangeRtParticipants>'
Can someone please point me in the right direction?
Hi Jeff,
If I get your intention right I think you only need to change few little
things in order to make your code work:
- N'<?xml version="1.0" encoding="utf-16"?>: the N indicates that the
XML doc is using UTF-16 encoding;
- if you want to apply the namespace uri
"http://tempuri.org/ExchangeRtParticipants.xsd “ to all elements underneath
'ExchangeRTParticipans' element than you don't need to specify a prefix for
this namespace. It will be considered the default namespace for each node
under the element that declares this namespace. So the namespace declaration
inside your xml instance will be:
<ExchangeRtParticipants
xmlns="http://tempuri.org/ExchangeRtParticipants.xsd">...
- However if you keep the prefix declaration in your top level element
<ExchangeRtParticipants
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd">...; that means
that only the elements that are declared using the prefix in their name are
bound to that specific namespace. In your case <AgreementParticipant> is NOT
declared using the prefix 'er' and because of that it is NOT part of
'"http://tempuri.org/ExchangeRtParticipants.xsd” namespace; it is part of the
'NoName' namespace. That is why when you query for
'//er:AgreementParticipant' using the 'er' prefix in your XPath query inside
the OpenXML clause nothing is returned; AgreementParticipant element DOES NOT
belongs to "http://tempuri.org/ExchangeRtParticipants.xsd" namespace, it
belongs to 'nonamespace' uri. So if you don’t want <AgreementParticipant> to
belong to namespace referent by ‘er’ than you need to change you XPath expr
to '//AgreementParticipant' in order to get values out of that element.
I’ve changed your sample code below to implement the solution where you
don’t specify a prefix to "http://tempuri.org/ExchangeRtParticipants.xsd”
namespace in your xml instance declaration so it will be the default
namespace for every node under your top level node. Please let me know if
that was your primary intention.
-- modified sample
DROP PROCEDURE foo
GO
create procedure foo (@.doc XML) -- use ntext if you are running SQLServer
2000
AS
BEGIN
DECLARE @.iDoc INT
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.doc, '<root
xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
SELECT ReinscReferenceNicknameTxt,
Identifier,
ReinscReferenceNameTxt,
ReinscTypeDsc,
SourceCurrencyTypeCode,
SourceCurrencyTypeId,
TargetCurrencyTypeCode,
TargetCurrencyTypeId,
BeginDt,
Rate,
RateSource,
ParticipantIsManaged
FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
WITH(
ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
Identifiernvarchar(100) '@.Identifier',
ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
BeginDtsmalldatetime '@.BeginDt',
Ratedecimal '@.Rate',
RateSourcenvarchar(1) '@.RateSource',
ParticipantIsManagedbit '@.ParticipantIsManaged'
)
EXEC sp_xml_removedocument @.iDoc
END
GO
EXEC foo 'N<?xml version="1.0" ?><ExchangeRtParticipants
xmlns="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
ReinscReferenceNicknameTxt="NACRE" Identifier="2"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
/><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
/></ExchangeRtParticipants>'
Thanks,
Ana Elisa - SDET - SQLServer Group
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff Spicolli" wrote:

> Hi,
> I can't seem to nail down the correct way to use OpenXml with input Xml that
> conatains a root namespace.
> '=================================
> PROC
> =================================
> <snip>
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.ExchangeRtMgmtXml, '<root
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
> INSERT INTO #ExchangeRtMgmt (
> ReinscReferenceNicknameTxt,
> Identifier,
> ReinscReferenceNameTxt,
> ReinscTypeDsc,
> SourceCurrencyTypeCode,
> SourceCurrencyTypeId,
> TargetCurrencyTypeCode,
> TargetCurrencyTypeId,
> BeginDt,
> Rate,
> RateSource,
> ParticipantIsManaged
> )
> SELECT ReinscReferenceNicknameTxt,
> Identifier,
> ReinscReferenceNameTxt,
> ReinscTypeDsc,
> SourceCurrencyTypeCode,
> SourceCurrencyTypeId,
> TargetCurrencyTypeCode,
> TargetCurrencyTypeId,
> BeginDt,
> Rate,
> RateSource,
> ParticipantIsManaged
> FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
> WITH(
> ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
> Identifiernvarchar(100) '@.Identifier',
> ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
> ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
> SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
> SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
> TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
> TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
> BeginDtsmalldatetime '@.BeginDt',
> Ratedecimal '@.Rate',
> RateSourcenvarchar(1) '@.RateSource',
> ParticipantIsManagedbit '@.ParticipantIsManaged'
> )
> EXEC sp_xml_removedocument @.iDoc
> </snip>
>
> '======================================
> ' Call SProc
> '======================================
> EXEC spr_ManagedExchangeRate_byParticipant '<?xml version="1.0"
> encoding="utf-16"?><ExchangeRtParticipants
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
> ReinscReferenceNicknameTxt="NACRE" Identifier="2"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
> /><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
> /></ExchangeRtParticipants>'
>
> Can someone please point me in the right direction?
>
|||Thanks Ana,
Part of the dilemma is a direct effect of the design pattern.
1) XML stream is passed into an Engine
2) Engine Consists of 4 Commands
3) Each command takes in a Stream and returns a subset stream (Xpath
Filtered via XSL)
4) All Streams are then merged back into a single stream.
I noticed stright away that if either XML stream conatained identical
Namespace's (Prefixed or not), then the issue was really not present as there
is a standard way to perform xpath queries on the XML.
The problem identified itself after the first 'pass' through.
Your reply was very helpful, thanks.
"Ana Elisa Schmidt [MSFT]" wrote:
[vbcol=seagreen]
> Hi Jeff,
> If I get your intention right I think you only need to change few little
> things in order to make your code work:
> - N'<?xml version="1.0" encoding="utf-16"?>: the N indicates that the
> XML doc is using UTF-16 encoding;
> - if you want to apply the namespace uri
> "http://tempuri.org/ExchangeRtParticipants.xsd “ to all elements underneath
> 'ExchangeRTParticipans' element than you don't need to specify a prefix for
> this namespace. It will be considered the default namespace for each node
> under the element that declares this namespace. So the namespace declaration
> inside your xml instance will be:
> <ExchangeRtParticipants
> xmlns="http://tempuri.org/ExchangeRtParticipants.xsd">...
> - However if you keep the prefix declaration in your top level element
> <ExchangeRtParticipants
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd">...; that means
> that only the elements that are declared using the prefix in their name are
> bound to that specific namespace. In your case <AgreementParticipant> is NOT
> declared using the prefix 'er' and because of that it is NOT part of
> '"http://tempuri.org/ExchangeRtParticipants.xsd” namespace; it is part of the
> 'NoName' namespace. That is why when you query for
> '//er:AgreementParticipant' using the 'er' prefix in your XPath query inside
> the OpenXML clause nothing is returned; AgreementParticipant element DOES NOT
> belongs to "http://tempuri.org/ExchangeRtParticipants.xsd" namespace, it
> belongs to 'nonamespace' uri. So if you don’t want <AgreementParticipant> to
> belong to namespace referent by ‘er’ than you need to change you XPath expr
> to '//AgreementParticipant' in order to get values out of that element.
>
> I’ve changed your sample code below to implement the solution where you
> don’t specify a prefix to "http://tempuri.org/ExchangeRtParticipants.xsd”
> namespace in your xml instance declaration so it will be the default
> namespace for every node under your top level node. Please let me know if
> that was your primary intention.
> -- modified sample
> DROP PROCEDURE foo
> GO
> create procedure foo (@.doc XML) -- use ntext if you are running SQLServer
> 2000
> AS
> BEGIN
> DECLARE @.iDoc INT
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.doc, '<root
> xmlns:er="http://tempuri.org/ExchangeRtParticipants.xsd"/>'
>
> SELECT ReinscReferenceNicknameTxt,
> Identifier,
> ReinscReferenceNameTxt,
> ReinscTypeDsc,
> SourceCurrencyTypeCode,
> SourceCurrencyTypeId,
> TargetCurrencyTypeCode,
> TargetCurrencyTypeId,
> BeginDt,
> Rate,
> RateSource,
> ParticipantIsManaged
> FROM OPENXML(@.iDoc, '//er:AgreementParticipant',1)
> WITH(
> ReinscReferenceNicknameTxtnvarchar(50) '@.ReinscReferenceNicknameTxt',
> Identifiernvarchar(100) '@.Identifier',
> ReinscReferenceNameTxtnvarchar(60) '@.ReinscReferenceNameTxt',
> ReinscTypeDscnvarchar(100) '@.ReinscTypeDsc',
> SourceCurrencyTypeCodenvarchar(3) '@.SourceCurrencyTypeCode',
> SourceCurrencyTypeIdint '@.SourceCurrencyTypeId',
> TargetCurrencyTypeCodenvarchar(3) '@.TargetCurrencyTypeCode',
> TargetCurrencyTypeIdint '@.TargetCurrencyTypeId',
> BeginDtsmalldatetime '@.BeginDt',
> Ratedecimal '@.Rate',
> RateSourcenvarchar(1) '@.RateSource',
> ParticipantIsManagedbit '@.ParticipantIsManaged'
> )
> EXEC sp_xml_removedocument @.iDoc
> END
> GO
> EXEC foo 'N<?xml version="1.0" ?><ExchangeRtParticipants
> xmlns="http://tempuri.org/ExchangeRtParticipants.xsd"><AgreementParticipant
> ReinscReferenceNicknameTxt="NACRE" Identifier="2"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="UKP"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="0"
> /><AgreementParticipant ReinscReferenceNicknameTxt="PRF1" Identifier="3"
> ReinscReferenceNameTxt="NET" ReinscTypeDsc="NET" SourceCurrencyTypeCode="FRN"
> SourceCurrencyTypeId="1" TargetCurrencyTypeCode="" TargetCurrencyTypeId="47"
> BeginDt="2005-06-18T00:00:00" Rate="0" RateSource="" ParticipantIsManaged="1"
> /></ExchangeRtParticipants>'
> Thanks,
> Ana Elisa - SDET - SQLServer Group
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jeff Spicolli" wrote: