Hi,
I have the following SQL which is building XML…
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:
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