problem declaring variable in query with xmlnamespe

Question

I have a stored procedure in which I am reading node's data from xml data type

Now i also have to declare some variable bu always get error if declaring it

WITH XMLNAMESPACES('http://idealliance.org/Specs/mailxml12.0a/mailxml_tm' AS p3)

declare @ConsigneeApptID int
declare @PartnerId int

Please let me know what would be the problem in the syntax

---

Kamran Shahid Principle Engineer Development (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

Answer 1

What ERROR message you are getting?

What is your XML structure? Can you provide some more code snippet so that we can know what is the real scenario?

b.t.w check following blog post to use namespaces with XML data: http://sqlwithmanoj.wordpress.com/2011/11/02/adding-namespaces-in-xml-querying-xml-with-namespaces/

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011

Answer 2

You need to use WITH XMLNAMESPACES right before the SELECT or INSERT statement, DECLARE first, then use WITH.

This works:

declare @ConsigneeApptID int
declare @PartnerId int
WITH XMLNAMESPACES('http://idealliance.org/Specs/mailxml12.0a/mailxml_tm' AS p3)
select 1

Regards
James

---

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 3

Hello,

The WITH XML is not a "stand alone" command, you have to use it together your other queries on the XML data.

Sample:

DECLARE @ecb XML; 
SET @ecb = (SELECT CONVERT(xml, EcbSrc.BulkColumn) AS XmlRates 
            FROM OPENROWSET(BULK N'D:\eurofxref-hist.xml' 
                           ,SINGLE_BLOB) AS EcbSrc); 
 
-- Selection of the rates; insert only missing values. 
;WITH XMLNAMESPACES 
     ( 'http://www.gesmes.org/xml/2002-08-01' as gesmes 
      ,DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref') 
,ecb AS 
   (SELECT Cubes.Rows.value('../@time', 'date') AS [ExcDate] 
          ,Cubes.Rows.value('@currency', 'char(3)') AS [Currency] 
          ,Cubes.Rows.value('@rate', 'decimal(19, 6)') AS [Rate] 
    FROM @ecb.nodes('/gesmes:Envelope/Cube/Cube/Cube') AS Cubes(Rows)) 
INSERT INTO dbo.EcbDailyExchangeRates 
    ([ExcDate] 
    ,[Currency] 
    ,[Rate]) 
SELECT ecb.[ExcDate] 
      ,ecb.[Currency] 
      ,ecb.[Rate] 
FROM ecb 
     LEFT JOIN dbo.EcbDailyExchangeRates AS Dst 
         ON Dst.ExcDate = ecb.ExcDate 
            AND Dst.Currency = ecb.Currency 
WHERE Dst.ExcDate IS NULL 
      AND NOT ecb.Currency IS NULL;

---

Olaf Helper

Blog Xing

Answer 4

James i wi try your suggestion.I think i got syntax error by declaring variabe above then xmlnamespace declaration but i will retry

---

Kamran Shahid Principle Engineer Development (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

Answer 5

If you are just setting a variable, you don't have to specify WITH XMLNAMESPACES, eg

DECLARE @xml XML

SET @xml = '<root xmlns:ns="yourNamespace">
	<a>b</a>
	<a>c</a>
</root>'

Answer 6

Thanks James,

My final sp looks like

ALTER PROCEDURE [dbo].[uspAddPartnerQueryResponseinDataProcessingQueueForTrackingAssurety] @ID INT
                    ,@ErrorVar int OUTPUT       
AS
  BEGIN
		DECLARE @return_value            int
                    , @DataProcessingQueueId INT

	  declare @ConsigneeApptID int
	  declare @Cntfind int
      declare @PartnerId int;      

      select @ConsigneeApptID = ConsigneeApptID
             , @PartnerId = PartnerID
      from   PartnerAppointmentQueryResponse
      where  Id = @ID

      IF( EXISTS(SELECT TOP 1 *
                 FROM   TrackAppointment
                 WHERE  ApptId = @ConsigneeApptID
                        and PartnerId = @PartnerId) )
        begin
           

            EXEC @return_value = uspAddDataProcessingQueue
              @DataSourceTypeId = 4
              , @DataContext = 'A'
              , @SourceRecordId = @ID
              , @DataProcessingQueueId = @DataProcessingQueueId OUTPUT
              , @ErrorVar = @ErrorVar OUTPUT
        end
  else
       begin
			set @Cntfind = 0;
			WITH XMLNAMESPACES('http://idealliance.org/Specs/mailxml12.0a/mailxml_tm' AS p3)
			
            SELECT @Cntfind = count(*)
			from TrackContainer tc
				inner join (
  							SELECT T.PartnerID,  T.ConsigneeApptID,
  							Cast(T2.x.value('(text())[1]', 'varchar(50)') as varchar(50)) AS Barcode
  							FROM
  								dbo.PartnerAppointmentQueryResponse AS T
  								CROSS APPLY
  								T.PartnerApptQueryResponseType.nodes('/PartnerApptQueryResponse/p3:QueryResults/p3:ApptBlock/p3:ContentDetailInfoUpdate/p3:ContentUSPSSummary') AS T1(x)
  								CROSS APPLY
  								T1.x.nodes('p3:Pallets//p3:USPSContainerInfo/p3:IMcb') AS T2(x)
  								WHERE PartnerApptQueryResponseType is not null
  								and T.ID = @ID
  							) Y
					on Y.Barcode = tc.IMBarcode
  					and tc.IsPallet =1
  					and tc.IMBarcode is not null
  					and tc.PartnerId = Y.PartnerID
  					and tc.ApptId is not null
  					and tc.ApptId <> Y.ConsigneeApptID
  					
  					
  					if(@Cntfind > 0)
  					begin
  						 EXEC @return_value = uspAddDataProcessingQueue
							  @DataSourceTypeId = 4
							  , @DataContext = 'A'
							  , @SourceRecordId = @ID
							  , @DataProcessingQueueId = @DataProcessingQueueId OUTPUT
							  , @ErrorVar = @ErrorVar OUTPUT
  					end
  					
       end
       
       IF @@ERROR <> 0
        SET @ErrorVar = -1      
       
  END

---

Kamran Shahid Principle Engineer Development (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])