Hi,

I have the following SQL which is building XML..

Code:
DECLARE @Passengers xml=NULL
SET @Passengers = '<Passengers> 
					   <Passenger>
						   <PaxID>446571</PaxID>
						   <PaxTitle>Mr</PaxTitle>
						   <PaxForename>Joe</PaxForename>
						   <PaxMiddleName></PaxMiddleName>
						   <PaxSurname>Bloggs</PaxSurname>
						   <DOB></DOB>
						   <PassportNo></PassportNo>
						   <IssuedDate></IssuedDate>
						   <ExpiryDate></ExpiryDate>
						   <IssuingCountry></IssuingCountry>
						   <Nationality></Nationality>
						   <SpecialRequests></SpecialRequests>
						   <OwnInsurance></OwnInsurance>
						   <OwnInsuranceNumber></OwnInsuranceNumber>
					   </Passenger>
					</Passengers>'
		
DROP TABLE #tmpPassengers

CREATE TABLE #tmpPassengers
			(	
				PaxID			int,
				PaxTitle			varchar(10),
				PaxForename		varchar(50),
				PaxMiddleName		varchar(50),
				PaxSurname		varchar(50),
				DOB				datetime,
				PassportNo			varchar(50),
				IssuedDate		datetime,
				ExpiryDate			datetime,
				IssuingCountry		int,
				Nationality			int,
				SpecialRequests		varchar(1000),
				OwnInsurance		int,
				OwnInsuranceNumber	varchar(255),
				IsRestricted		int
			) 

BEGIN TRY

--Populate values of temp table
INSERT INTO #tmpPassengers     
		   (    
				PaxID,
				PaxTitle,
				PaxForename,
				PaxMiddleName,
				PaxSurname,
				DOB,
				PassportNo,
				IssuedDate,
				ExpiryDate,
				IssuingCountry,
				Nationality,
				SpecialRequests,
				OwnInsurance,
				OwnInsuranceNumber
		   )    
SELECT	Pax.query('./PaxID').value('.', 'int') PaxID,
		Pax.query('./PaxTitle').value('.', 'varchar(10)') PaxTitle,
		Pax.query('./PaxForename').value('.', 'varchar(50)') PaxForename,
		Pax.query('./PaxMiddleName').value('.', 'varchar(50)') PaxMiddleName,
		Pax.query('./PaxSurname').value('.', 'varchar(50)') PaxSurname,
		Pax.query('./DOB').value('.', 'datetime') DOB,
		Pax.query('./PassportNo').value('.', 'int') PassportNo,
		Pax.query('./IssuedDate').value('.', 'datetime') IssuedDate,
		Pax.query('./ExpiryDate').value('.', 'datetime') ExpiryDate,
		Pax.query('./IssuingCountry').value('.', 'int') IssuingCountry,
		Pax.query('./Nationality').value('.', 'int') Nationality,
		Pax.query('./SpecialRequests').value('.', 'varchar(1000)') SpecialRequests,
		Pax.query('./OwnInsurance').value('.', 'int') OwnInsurance,
		Pax.query('./OwnInsuranceNumber').value('.', 'varchar(255)') OwnInsuranceNumber
FROM	@Passengers.nodes('Passengers/Passenger') AS Passengers(Pax)   

END TRY    

BEGIN CATCH    
    RAISERROR('Err_Pax_XML_Invalid', 16, 1)    
    RETURN    
END CATCH
Now when i run the above query it returns no errors which is fine. BUT when i try passing in this XML instead:

Code:
SET @Passengers = '<Passenger>
						   <PaxID>446571</PaxID>
						   <PaxTitle>Mr</PaxTitle>
						   <PaxForename>Joe</PaxForename>
						   <PaxMiddleName></PaxMiddleName>
						   <PaxSurname>Bloggs</PaxSurname>
						   <DOB></DOB>
						   <PassportNo></PassportNo>
						   <IssuedDate></IssuedDate>
						   <ExpiryDate></ExpiryDate>
						   <IssuingCountry></IssuingCountry>
						   <Nationality></Nationality>
						   <SpecialRequests></SpecialRequests>
						   <OwnInsurance></OwnInsurance>
						   <OwnInsuranceNumber></OwnInsuranceNumber>
					   </Passenger>'
When i run this, it ALSO does not return any errors it should RAISE ERROR 'Err_Pax_XML_Invalid' because it is not wrapped inside <Passengers></Passengers>..

Can anyone please help me fix this so that when i pass in the XML without it being wrapped inside Passengers it RAISES the error.

Many thanks,
Billy