Output as xml

Question

Output as xml

wjhamel

Old Hand

Points: 347
More actions
December 8, 2011 at 9:37 am

#247995

The following is from a view. I need to output the entire result set as xml, using FOR XML, but i'm completely unfamiliar with the syntax with which to do so and have found little online to guide me in this. What code is used at the end of this to output it as xml??:
CREATE VIEW [dbo].[BoatingWarningasXML]
AS
SELECT dbo.BoatingWarning.UniqueKey,
dbo.BoatingWarning.CitationNo,
dbo.BoatingWarning.CitationDateTime,
dbo.BoatingWarning.LoginName,
dbo.BoatingWarning.UniqueUserID,
dbo.BoatingWarning.Void,
dbo.BoatingWarning.Companion,
dbo.BoatingWarning.CompanionNumberType,
dbo.BoatingWarning.CompanionNumber,
dbo.BoatingWarning.CompanionUniqueKey,
dbo.BoatingWarning.CountyOf,
dbo.BoatingWarning.CountyOfNo,
dbo.BoatingWarning.CityOf,
dbo.BoatingWarning.CityOfNo,
dbo.BoatingWarning.OfficerAgency,
dbo.BoatingWarning.Location,
dbo.BoatingWarning.Latitude,
dbo.BoatingWarning.Longitude,
dbo.BoatingWarning.MNINo,
dbo.BoatingWarning.NameFirst,
dbo.BoatingWarning.NameMiddle,
dbo.BoatingWarning.NameLast,
dbo.BoatingWarning.NameSuffix,
dbo.BoatingWarning.Street,
dbo.BoatingWarning.AddressOther,
dbo.BoatingWarning.AddDiffThanReg,
dbo.BoatingWarning.City,
dbo.BoatingWarning.State,
dbo.BoatingWarning.ZipCode,
dbo.BoatingWarning.Phone,
dbo.BoatingWarning.DateOfBirth,
dbo.BoatingWarning.Race,
dbo.BoatingWarning.Ethnicity,
dbo.BoatingWarning.Sex,
dbo.BoatingWarning.Height,
dbo.BoatingWarning.Weight,
dbo.BoatingWarning.Hair,
dbo.BoatingWarning.Eyes,
dbo.BoatingWarning.BusinessName,
dbo.BoatingWarning.BusinessPhone,
dbo.BoatingWarning.IDNo,
dbo.BoatingWarning.IDState,
dbo.BoatingWarning.IDType,
dbo.BoatingWarning.IDExpires,
dbo.BoatingWarning.VesselRegNo,
dbo.BoatingWarning.VesselRegState,
dbo.BoatingWarning.VesselRegExpires,
dbo.BoatingWarning.VesselDocNo,
dbo.BoatingWarning.VesselFuel,
dbo.BoatingWarning.VesselPropulsion,
dbo.BoatingWarning.VesselHP,
dbo.BoatingWarning.VesselYear,
dbo.BoatingWarning.VesselMake,
dbo.BoatingWarning.VesselType,
dbo.BoatingWarning.VesselLength,
dbo.BoatingWarning.VesselLengthType,
dbo.BoatingWarning.VesselColor,
dbo.BoatingWarning.VesselHIN,
dbo.BoatingWarning.VehicleYear,
dbo.BoatingWarning.VehicleMake,
dbo.BoatingWarning.VehicleModel,
dbo.BoatingWarning.VehicleTagNo,
dbo.BoatingWarning.VehicleTagNoState,
dbo.BoatingWarning.VehicleTagExpires,
dbo.BoatingWarning.VehicleVIN,
dbo.BoatingWarning.VehicleColor,
dbo.BoatingWarning.OfficerNotes,
dbo.BoatingWarning.OfficerOrgUnit,
dbo.BoatingWarning.OfficerRank,
dbo.BoatingWarning.OfficerName,
dbo.BoatingWarning.OfficerIDNo,
dbo.BoatingWarning.OfficerSignature,
dbo.BoatingWarning.UserCreatedDateTime,
dbo.BoatingWarning.Printed,
dbo.BoatingWarning.PrintedDateTime,
dbo.BoatingWarning.UserCompleted,
dbo.BoatingWarning.UserCompletedDateTime,
dbo.BoatingWarning.UserTransmitted,
dbo.BoatingWarning.UserTransmittedDateTime,
dbo.BoatingWarning.SystemTransmitAck,
dbo.BoatingWarning.SystemTransmitAckDateTime,
dbo.BoatingWarning.RuleNumber,
dbo.BoatingWarning.FishSpecies,
dbo.BoatingWarning.FishComments,
dbo.BoatingWarning.HuntSpecies,
dbo.BoatingWarning.HuntComments,
dbo.BoatingWarning.QualSpecies,
dbo.BoatingWarning.QualComments,
dbo.BoatingWarning.OtherViolation1,
dbo.BoatingWarning.OtherViolation2,
dbo.BoatingWarning.CompanionNTNumberType,
dbo.BoatingWarning.ReportStatus,
dbo.BoatingWarning.ViolationUniqueKey,
dbo.BoatingWarning.Violation,
dbo.BoatingWarning.ViolationTypeCode,
dbo.BoatingWarning.ViolationType,
dbo.BoatingWarning.ViolationLevelCode,
dbo.BoatingWarning.ViolationLevel,
dbo.BoatingWarning.ViolationLevelCourtAppearanceMandatory,
dbo.BoatingWarning.ViolationDescription,
dbo.BoatingWarning.ViolationInstructions,
dbo.BoatingWarning.ViolationCode,
dbo.BoatingWarning.CodeViolationUniqueKey,
dbo.BoatingWarning.CodeViolation,
dbo.BoatingWarning.CodeViolationDescription,
dbo.BoatingWarning.CodeViolationInstructions,
dbo.BoatingWarning.CodeViolationCounty,
dbo.BoatingWarning.DescriptionOfViolations,
dbo.BoatingWarning.OfficerAgencyGroup,
dbo.BoatingWarning.WarningBoatCodesExist AS WBCE,
dbo.BoatingWarning.WarningFishCodesExist AS WFCE,
dbo.BoatingWarning.WarningHuntCodesExist AS WHCE,
dbo.BoatingWarning.WarningQualityCodesExist WQCE,
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode,'') AS HWC,
ISNULL(dbo.BoatingWarningQualityCodes.WarningCode,'') AS HWC,
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode,'') AS HWC,
ISNULL(dbo.BoatingWarningFishCodes.WarningCode,'') AS HWC
FROM dbo.BoatingWarning
LEFT OUTER JOIN dbo.BoatingWarningBoatCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningHuntCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningHuntCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningQualityCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningQualityCodes.BoatingWarningUniqueFKey
LEFT OUTER JOIN dbo.BoatingWarningFishCodes
ON dbo.BoatingWarning.UniqueKey = dbo.BoatingWarningFishCodes.BoatingWarningUniqueFKey
BoatingWarning FOR XML AUTO, ELEMENTS, TYPE
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You need to delete the BoatingWarning that you wrote in the last line just before the FOR XML. By the way, there are many ways to get it as XML and many formats that can be used. If you have to create it as XML, you have to learn how to do it and not just relay on this forum.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

wjhamel Old Hand Points: 347 More actions · Answer 2

Thank you for that advice, but it's not my job to format the xml. It is just my job to create the view and have it output to xml. It's someone else's task to format the xml.