RE: Root Namespace Prefix and OpenXml

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:

>
> 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(
> ReinscReferenceNicknameTxt nvarchar(50) '@ReinscReferenceNicknameTxt',
> Identifier nvarchar(100) '@Identifier',
> ReinscReferenceNameTxt nvarchar(60) '@ReinscReferenceNameTxt',
> ReinscTypeDsc nvarchar(100) '@ReinscTypeDsc',
> SourceCurrencyTypeCode nvarchar(3) '@SourceCurrencyTypeCode',
> SourceCurrencyTypeId int '@SourceCurrencyTypeId',
> TargetCurrencyTypeCode nvarchar(3) '@TargetCurrencyTypeCode',
> TargetCurrencyTypeId int '@TargetCurrencyTypeId',
> BeginDt smalldatetime '@BeginDt',
> Rate decimal '@Rate',
> RateSource nvarchar(1) '@RateSource',
> ParticipantIsManaged bit '@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(
> > ReinscReferenceNicknameTxt nvarchar(50) '@ReinscReferenceNicknameTxt',
> > Identifier nvarchar(100) '@Identifier',
> > ReinscReferenceNameTxt nvarchar(60) '@ReinscReferenceNameTxt',
> > ReinscTypeDsc nvarchar(100) '@ReinscTypeDsc',
> > SourceCurrencyTypeCode nvarchar(3) '@SourceCurrencyTypeCode',
> > SourceCurrencyTypeId int '@SourceCurrencyTypeId',
> > TargetCurrencyTypeCode nvarchar(3) '@TargetCurrencyTypeCode',
> > TargetCurrencyTypeId int '@TargetCurrencyTypeId',
> > BeginDt smalldatetime '@BeginDt',
> > Rate decimal '@Rate',
> > RateSource nvarchar(1) '@RateSource',
> > ParticipantIsManaged bit '@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?
> >
.

Loading