openxml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

openxml

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-20 : 07:27:00

Jason writes "How Can I Get OpenXML to work with a root namespace prefix?

I thought this code should work, but it does not.

=================
XML
=================
<?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>

=====================================
SQL
====================================

if exists (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].[spr_ManagedExchangeRate_byParticipant]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spr_ManagedExchangeRate_byParticipant]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.spr_ManagedExchangeRate_byParticipant
(
@ExchangeRtMgmtXml ntext
)
AS

/***************************************************************************
** Procedure Name: spr_ManagedExchangeRate_byParticipant
** Description: This procedure retrieves the Managed Exchange Rate(s) from
** CSS dbo.ManagedCurrencies table based on the input XML.
**
** Return values: Row or Rows
** Input Params: ExchangeRtMgmtXml
**
**
** Output Params: Managed Exchange Rate Row or Rows
** Auther: Xiao Shen
** Date: June. 3, 2005
****************************************************************************/

SET NOCOUNT ON

DECLARE @Error INT
DECLARE @iDoc INT

/* Create temp table #ExchangeRtMgmt */
CREATE TABLE #ExchangeRtMgmt
(
ReinscReferenceNicknameTxt nvarchar(50),
Identifier nvarchar(100),
ReinscReferenceNameTxt nvarchar(60),
ReinscTypeDsc nvarchar(100),
SourceCurrencyTypeCode nvarchar(3),
SourceCurrencyTypeId int,
TargetCurrencyTypeCode nvarchar(3),
TargetCurrencyTypeId int,
BeginDt smalldatetime,
Rate decimal,
RateSource nvarchar(1),
ParticipantIsManaged bit default (0)
)
CREATE INDEX i_#ExchangeRtMgmt ON #ExchangeRtMgmt (SourceCurrencyTypeId
,TargetCurrencyTypeId)

/* populate temp table #ExchangeRtMgmt from the
@ExchangeRtMgmtXml string*/
EXEC sp_xml_preparedocument @iDoc OUTPUT, @ExchangeRtMgmtXml, '<ExchangeRtParticipants 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:ExchangeRtParticipants/er:AgreementParticipant',1)
WITH(
ReinscReferenceNicknameTxt nvarchar(50) '@ReinscReferenceNicknameTxt',

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources