Views, "Output", XML, Confusion.

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

Views, "Output", XML, Confusion.

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-10 : 19:17:15

Questions. I have Questions.
So, earlier this week I was tasked with creating this view, that was to push it’s output in XML format. Below is the code for that view, minus the boring, repetitive portion of the select in the middle. At the end of this week, the task was expanded and I was told to make “that” view look like “this” one. The difference for the 1st view was to incorporate the “output” command that you see in the beginning of the second view. Before I get into “how” I incorporate that command (syntax nightmares and all), it would really help me to understand exactly what effect that “output” command is intended to accomplish. I’m finding very little information in the wild about the purpose of the command. Any information you can provide me on this issue is appreciated.
View 1:
USE [MobileFormsServer]
GO
SELECT
(Select dbo.Boatingwarning.uniquekey,
dbo.BoatingWarning.CitationNo,
dbo.BoatingWarning.CitationDateTime,
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 QWC,
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode,'') AS BWC,
ISNULL(dbo.BoatingWarningFishCodes.WarningCode,'') AS FWC
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
FOR XML AUTO, ELEMENTS, TYPE) AS t
GO

View 2:
USE [MobileFormsServer]
GO
SELECT
[UniqueKey],
[OUTPUT] = CONVERT(varchar(max),(
SELECT * FROM (SELECT
/*N*/ [UniqueKey] = cit.UniqueKey,
/*1*/ [UpdateType] = 'A',
/*2*/ [CitationNumber] = REPLACE(cit.CitationNo,'-',''),
/*3*/ [CheckDigit] = dbo.fnsc_CalculateCheckDigit(cit.CitationNo),
/*124*/ [FineAmount] = '',
/*125*/ [FormVersion] = '',--HSMV 75904 (Rev. 10/08)', --FORM_VERSION
/*126*/ [IssueDate] = '' --ISSUE_DATE
FROM TrafficDUI dui with (nolock)
WHERE dui.UniqueKey = tc.UniqueKey
) TrafficCitation
FOR XML AUTO, ELEMENTS, TYPE

So again, the first question is, what is the purpose of the “USE [MobileFormsServer]
GO
SELECT
[UniqueKey],
[OUTPUT] = CONVERT(varchar(max),(
SELECT * FROM (SELECT”
Addition to the first view???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 00:58:42

USE [MobileFormsServer]
GO

will make sure code is run against context of MobileFormsServer database

SELECT
[UniqueKey],
[OUTPUT] = CONVERT(varchar(max),(
SELECT * FROM (SELECT”

you've trying to create resultset with two columns UniqueKey and OUTPUT and forming an XML out of them using FOR XML AUTO

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-11 : 13:05:29

Thank you. That is helpful. I did notice, with the first view, as it output it's xml, it was one column and one row, with all of the XML mashed into that one cell. So, by incorporating the "output" statement, should i expect that one resultset will be the same xml as the one column in the first view and the second resultset would be the uniquekey?, Both, obviously as xml.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 13:22:58

nope.. its not OUTPUT which makes XML but its FOR XML which builds xml document with two columns as nodes inside one uniquekey and other OUTPUT column

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-11 : 17:44:34

No, i know that, but it's OUTPUT which gives me the second resultset. correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 23:36:39

yep. OUTPUT gives you second resultset inside XML

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 09:12:32

Ok, so at this point, when trying to incopororate the OUTPUT statement for the UNIQUEKEY as the other resultset, i'm getting two different syntax errors. The first is on the select for Uniquekey. it's telling me that this is an invalid column. I still get this message if i used the fully qualified name for that table and column. The other error is at the end of the full script where i have "as t". It's simply stating "incorrect syntax". So obviously, my syntax is wrong in more than one place, but i'm not seeing it. Any suggestions?

USE [MobileFormsServer]
GO

/****** Object: View [dbo].[vwsc_BoatingWarningasXML] Script Date: 12/12/2011 08:03:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwsc_BoatingWarningasXML]
AS

SELECT
[UniqueKey], [OUTPUT] = CONVERT(varchar(max),
(Select dbo.Boatingwarning.uniquekey,
dbo.BoatingWarning.CitationNo,
dbo.BoatingWarning.CitationDateTime,
dbo.BoatingWarning.LoginName,
dbo.BoatingWarning.UniqueUserID,
dbo.BoatingWarning.Void,
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode,'') AS HWC,
ISNULL(dbo.BoatingWarningQualityCodes.WarningCode,'') AS QWC,
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode,'') AS BWC,
ISNULL(dbo.BoatingWarningFishCodes.WarningCode,'') AS FWC
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
FOR XML AUTO, ELEMENTS, TYPE) AS t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:45:06

one question. which is field you're trying to convert to varchar(max)?

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 09:48:01

Uniquekey from the table BoatingWarning

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:53:00

then whats the large query thats following? you want it as an XML? Also should UniqueKey also come inside XML?

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 09:54:42

The derived table (the larger select statment) is the rest of the select fromthe same source table, minus the uniquekey, which, for some reason, needs to be selected as it's own column within the same resultset, but also as xml.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 09:57:40

sorry didnt get that. can you show how will be your resultset in above case?

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 10:01:06

i have no data in those tables currently, but the resultset should look like this:
Uniquekey Output
NULL NULL

Of course, with the data for each formatted as xml.

So, from what i understand i'm being tasked with, the larger derived table and it's selects will be the "Output" and the Uniquekey will be the Uniquekey column in this view. If this doesn't make it clear, i'll try to import some data from another server here and populate my local tables to produce a clearer resultset for you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:11:41

how would Unique key be an xml value? its a numeric value as i understand

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 10:16:54

i've looked at the data on another server for a similarly layed out table. It appears i do need the uniquekey value to fall into that column just as it appears (as the alphnumerical data it is, not as xml), but, in the same resultset as the OUTPUT's xml data, in it's own column.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 10:24:01

so, then it would also mean that the data that i'm converting to varchar max is not the uniquekey, but rather, the rest of the derived table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:24:41

do you mean this then?


CREATE VIEW [dbo].[vwsc_BoatingWarningasXML]
AS

SELECT 
(Select [UniqueKey],dbo.Boatingwarning.uniquekey, 
dbo.BoatingWarning.CitationNo,
dbo.BoatingWarning.CitationDateTime,
dbo.BoatingWarning.LoginName,
dbo.BoatingWarning.UniqueUserID,
dbo.BoatingWarning.Void,
ISNULL(dbo.BoatingWarningHuntCodes.WarningCode,'') AS HWC,
ISNULL(dbo.BoatingWarningQualityCodes.WarningCode,'') AS QWC,
ISNULL(dbo.BoatingWarningBoatCodes.WarningCode,'') AS BWC,
ISNULL(dbo.BoatingWarningFishCodes.WarningCode,'') AS FWC
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
WHERE [UniqueKey]=b.[UniqueKey]
FOR XML AUTO, ELEMENTS) AS t(u),
[UniqueKey]
FROM dbo.BoatingWarning b

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 11:23:02

What is the (u) related to in the FOR XML line?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:23:50

its just alias for column

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 11:39:05

Ugh. With those changes, it's telling me that [Uniquekey] is an ambiguous column name and that (u) alias in the FOR XML line throws that whole line into syntax error. It also states the second [uniquekey] value in the where clause cannot be bound.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-12 : 11:53:00

Hang on, i may have figured it out.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources