Working on a view to output to xml

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

General SQL Server Forums

New to SQL Server Programming

Working on a view to output to xml

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-07 : 14:29:31

I'm working on a new view and could use some guidance. The task is to create a view which produces output to xml. I'll be stuck on "that" part when i get to it. For now, i'm uncertain about how to get the select statement to perform a lookup on values in four bit fields. If any of the following bit fields are true, then the view will need to look up the corresponding WarningCode in the corresponding table: WarningBoatCodesExist, WarningFishCodesExist, WarningHuntCodesExist, WarningQualityCodesExist.

For example, if a record indicates that a WarningQualityCodeExists flag is set to true, then you would use the uniquekey value of the BoatingWarning record to look into the BoatingWarningQaulityCodes table, comparing the BoatingWarning uniquekey value to the BoatingWarningUniqueFKey and find the WarningCode.

So how would the syntax look to get this lookup done? My guess is the use of a case expression? WarningBoatCodesExist case when 1 then...????
Thanks!

Bangers and Mash are neither "Bangers" nor "Mash".

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-07 : 18:10:50

let me provide more information on what i'm trying to do. Below is the create script that i have so far (still working on it). With regard to the bit fields, i need to check the value for the four bit fields ( dbo.BoatingWarning.WarningBoatCodesExist,
dbo.BoatingWarning.WarningFishCodesExist, dbo.BoatingWarning.WarningHuntCodesExist,
dbo.BoatingWarning.WarningQualityCodesExist) and if it is "1" (true) then i need to do a left outer join like the following:
WHEN 1 THEN BWQC = BoatingWarning.UniqueKey
FROM MobileFormsServer.dbo.BoatingWarning BoatingWarning
LEFT OUTER JOIN
MobileFormsServer.dbo.BoatingWarningQualityCodes
BoatingWarningQualityCodes
ON (BoatingWarning.UniqueKey =
BoatingWarningQualityCodes.BoatingWarningUniqueFKey)
dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey,
dbo.BoatingWarning.UniqueKey
FROM dbo.BoatingWarning

and have it get the value of dbo.BoatingWarningBoatCodes.WarningCode based on that join. here is the full script:

EDITED:

USE [MobileFormsServer]
GO

/****** Object: View [dbo].[BoatingWarningasXML] Script Date: 12/07/2011 19:38:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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,
dbo.BoatingWarning.WarningFishCodesExist,
dbo.BoatingWarning.WarningHuntCodesExist,
dbo.BoatingWarning.WarningQualityCodesExist,
dbo.BoatingWarningBoatCodes.BoatingWarningUniqueFKey AS BWBC,

dbo.BoatingWarningFishCodes.BoatingWarningUniqueFKey AS BWFC,

dbo.BoatingWarningQualityCodes.BoatingWarningUniqueFKey AS BWQC,

dbo.BoatingWarningHuntCodes.BoatingWarningUniqueFKey AS BWHC,

dbo.BoatingWarningHuntCodes.WarningCode AS HWC,

dbo.BoatingWarningQualityCodes.WarningCode AS QWC,

dbo.BoatingWarningBoatCodes.WarningCode AS BWC,

dbo.BoatingWarningFishCodes.WarningCode AS FWC

FROM dbo.BoatingWarning

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

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 00:49:04

can i ask why you need a view for this? looks like what you need is a procedure instead based on bit field values passed.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 06:18:33

A question above my pay grade. However, from what i'm allowed to know, the rest of our software which uses these db's relies on the views to generate reports and other output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 06:55:52

quote:
Originally posted by WJHamel

A question above my pay grade. However, from what i'm allowed to know, the rest of our software which uses these db's relies on the views to generate reports and other output.

but eventually it returns a single xml value isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 07:02:03

my understanding is that it is returning all of the select values as xml, or that is the intent.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 07:37:57

your posted view code doesnt even have a FROM part. so I wonder how it works!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 09:51:10

the from is there, somehow it got left out of the cut and paste to here:
FROM dbo.BoatingWarning

I'm going to edit the entry to show the fields that are actually part of the select, then explain further.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 09:56:51

Ok, i've edited that entry. Now, first of all, in the select, i need BWC, HWC, QWC, and FWC to get their values based on comparing the BoatingWarning uniquekey value to the BoatingWarningUniqueFKey. That is the first obstacle. The next step is understanding how to use FOR XML to output all of it to xml.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 10:02:10

ok...that might make more sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:07:01

I would left outer join all of the 4 code tables. You could then use the query as a derived table and wrap it. In the outer query utilize a case statement to take the message that you want.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 10:07:30

Also, the uniquekey value from dbo.boatingwarning does not need to be in the select statement, nor in the output, but it will obviously be used in the join statements.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 10:17:28

i believe my code is already doing the left outer join of those 4 tables:
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

how do i incorporate the case statement to set the value of warningcode for each of those tables?

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:21:16

Let me get this straight, if all 4 warning codes existed, you would include all four of them - correct?

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:23:05

... so the column output would always have all 4 warning code fields with fields with existent warnings being null? Or are they mutually exclusive and you only return one warning field?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 10:24:25

For each of these (i just added the aliases) (We're looking for a true value here):
dbo.BoatingWarning.WarningBoatCodesExist AS WBCE,
dbo.BoatingWarning.WarningFishCodesExist AS WFCE,
dbo.BoatingWarning.WarningHuntCodesExist AS WHCE,
dbo.BoatingWarning.WarningQualityCodesExist WQCE,

Each of these would be assigned a value:
dbo.BoatingWarningHuntCodes.WarningCode AS HWC,
dbo.BoatingWarningQualityCodes.WarningCode AS QWC,
dbo.BoatingWarningBoatCodes.WarningCode AS BWC,
dbo.BoatingWarningFishCodes.WarningCode AS FWC

Based on the value in each of the code tables and the association between the UniqueKeyvalue in dbo.boatingWarning and the UniqueFkey value in each of the Codes tables.

Am i making sense?

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:24:59

also, just a recommendation. I would use aliases on the table names so you don't have to fully qualify each column instance with dbo.tablename. It would clean the query up a little - make it a little more readable.

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:28:03

yes, that makes total sense but... if you are returning all 4 warning code fields in the view output (and maybe some or all of them are null) you don't really need to do anything explicitly. Just simply left outer join the tables, if they exist the, the warningcode field will contain the value. If they don't exist, it will be null. I guess the part I'm confused about is whether you return all for warningcode fields regardless of whether or not they are null.

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:31:52

... in this case, the bit telling you whether the warningcode exists is not necessary. The left outer join by it's very definition will return the code if it exists or return null if it does not.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-08 : 10:34:48

i'm waiting for an answer on that myself. Expecting it momentarily.

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 10:40:16

The way I see it there could be 1 of 3 possible solutions.

1) Return all 4 codes in which case you have to do nothing special other than left join the code tables. Null values will exist in the fields for which a code does not exist.
2) They are mutually exclusive. Resolve the 4 fields down to a single field using a case statement to distill the field that is not null.
3) Multiple codes could exist (any or all of the 4 fields) and you want to return only the highest priority code. Again, resolve the 4 fields into 1 utilizing a case statement which build in the priority (highest priority on top).

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources