More pain with for XML Explicit

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

SQL Server 2005 Forums

Transact-SQL (2005)

More pain with for XML Explicit

Author

Topic

NicJ
Starting Member

15 Posts

Posted - 2009-05-28 : 09:50:52

OK

Create a database called dummy

then run this to generate some tables:-
=======================================

USE [dummy]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Linxdatatypes]') AND type in (N'U'))
DROP TABLE [dbo].[Linxdatatypes]
GO

USE [dummy]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Linxdatatypes](
[types] [varchar](20) NOT NULL,
[typesid] [smallint] NOT NULL,
[Name] [varchar](40) NOT NULL,
[Description] [varchar](60) NOT NULL,
[Basictype] [varchar](40) NULL,
[FK_Alias] [smallint] NULL,
[DecimalPlaces] [smallint] NULL,
PRIMARY KEY CLUSTERED
(
[typesid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fk_typesid]') AND parent_object_id = OBJECT_ID(N'[dbo].[Linxdatatypesvalues]'))
ALTER TABLE [dbo].[Linxdatatypesvalues] DROP CONSTRAINT [fk_typesid]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Linxdatatypesvalues]') AND type in (N'U'))
DROP TABLE [dbo].[Linxdatatypesvalues]
GO

CREATE TABLE [dbo].[Linxdatatypesvalues](
[typesid] [smallint] NOT NULL,
[value] [smallint] NOT NULL,
[Description] [varchar](60) NOT NULL,
PRIMARY KEY CLUSTERED
(
[typesid] ASC,
[value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Linxdatatypesvalues] WITH CHECK ADD CONSTRAINT [fk_typesid] FOREIGN KEY([typesid])
REFERENCES [dbo].[Linxdatatypes] ([typesid])
GO

ALTER TABLE [dbo].[Linxdatatypesvalues] CHECK CONSTRAINT [fk_typesid]
GO
=============================

Run this to add some data

==================

USE [dummy];
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[Linxdatatypes]([types], [typesid], [Name], [Description], [Basictype], [FK_Alias], [DecimalPlaces])
SELECT N'NumericType', 0, N'U32_t', N'Unsigned 32-bit value', N'unsigned long', NULL, NULL UNION ALL
SELECT N'NumericType', 1, N'U16_t', N'Unsigned 16-bit value', N'unsigned short', NULL, NULL UNION ALL
SELECT N'NumericType', 2, N'U8_t', N'Unsigned 8-bit value', N'unsigned char', NULL, NULL UNION ALL
SELECT N'NumericType', 3, N's32_t', N'Signed 32-bit value', N'unsigned long', NULL, NULL UNION ALL
SELECT N'NumericType', 4, N's16_t', N'Signed 16-bit value', N'unsigned short', NULL, NULL UNION ALL
SELECT N'NumericType', 5, N's8_t', N'Signed 8-bit value', N'unsigned char', NULL, NULL UNION ALL
SELECT N'NumericType', 6, N'boolean_t', N'Boolean value', N'unsigned long', NULL, NULL UNION ALL
SELECT N'NumericType', 7, N'extendedData_t', N'Extended data pointer', N'unsigned char*', NULL, NULL UNION ALL
SELECT N'NumericType', 8, N'voltage_t', N'Volts in tenths of a Volt', NULL, 0, 1 UNION ALL
SELECT N'NumericType', 9, N'test_t', N'Test data type', NULL, 0, NULL UNION ALL
SELECT N'EnumType', 10, N'enumLightbarFlashingMode_t', N'Lightbar settings|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 11, N'enumButtonStatus_t', N'Push button status|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 12, N'enumButtonPress_t', N'Push button pushes|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 13, N'enumIgnitionSwitch_t', N'State of ignition switch|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 14, N'enumVehicleBatteryState_t', N'Vehicle battery state|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 15, N'enumVehicleBatteryThreshold_t', N'Threshold voltages for vehicle battery|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 16, N'enumLightbarAdvancedFlashingMode_t', N'Lightbar settings|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 17, N'enumDeviceState_t', N'State of device|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 18, N'enumCameraFlashState_t', N'Camera flash state|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 19, N'enumDeviceRequiredState_t', N'Required state of device|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 20, N'enumDockLinxPresenceState_t', N'LINX present in dock state|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 21, N'enumDockLockStatus_t', N'Status of docking station lock|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 22, N'enumDockStatus_t', N'Status of docking|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 23, N'enumDockPowerStatus_t', N'Power state in docking station|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 24, N'enumLedColour_t', N'Colour of LED|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 25, N'enumLedState_t', N'On, off or flashing state of LED|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 26, N'enumLedPairState_t', N'On, off or flashing state of LED|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 27, N'enumTemperatureState_t', N'LINX thermal state|', NULL, NULL, NULL UNION ALL
SELECT N'EnumType', 28, N'enumTemperatureThreshold_t', N'Threshold temperatures for LINX|', NULL, NULL, NULL UNION ALL
SELECT N'NumericType', 29, N'temperature_t', N'Temperature in tenths of a degree Celsius', NULL, 3, 1 UNION ALL
SELECT N'NumericType', 30, N'batteryLevel_t', N'Battery charge level percentage', NULL, 0, NULL UNION ALL
SELECT N'EnumType', 31, N'enumBatteryState_t', N'Battery state|', NULL, NULL, NULL
COMMIT;
RAISERROR (N'[dbo].[Linxdatatypes]: Loaded the datatypes', 10, 1) WITH NOWAIT;
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[Linxdatatypesvalues]([typesid], [value], [Description])
SELECT 10, 0, N'FlashFast' UNION ALL
SELECT 10, 1, N'FlashSlow' UNION ALL
SELECT 11, 1, N'ButtonNotPushed' UNION ALL
SELECT 11, 2, N'ButtonPushed' UNION ALL
SELECT 12, 1, N'ButtonMomentaryPush' UNION ALL
SELECT 12, 2, N'ButtonPushAndHold' UNION ALL
SELECT 12, 3, N'ButtonRelease' UNION ALL
SELECT 13, 1, N'IgnitionSwitchOff' UNION ALL
SELECT 13, 2, N'IgnitionSwitchOn' UNION ALL
SELECT 14, 1, N'VehicleBatteryStateNormal' UNION ALL
SELECT 14, 2, N'VehicleBatteryStateLow' UNION ALL
SELECT 14, 3, N'VehicleBatteryStateVeryLow' UNION ALL
SELECT 14, 4, N'VehicleBatteryStateHigh' UNION ALL
SELECT 15, 1, N'VehicleBatteryLowThresholdDown' UNION ALL
SELECT 15, 2, N'VehicleBatteryLowThresholdUp' UNION ALL
SELECT 15, 3, N'VehicleBatteryVeryLowThresholdDown' UNION ALL
SELECT 15, 4, N'VehicleBatteryVeryLowThresholdUp' UNION ALL
SELECT 15, 5, N'VehicleBatteryHighThresholdDown' UNION ALL
SELECT 15, 6, N'VehicleBatteryHighThresholdUp' UNION ALL
SELECT 16, 0, N'Flash1Hz' UNION ALL
SELECT 16, 1, N'Flash2Hz' UNION ALL
SELECT 16, 2, N'Flash4Hz' UNION ALL
SELECT 16, 3, N'Flash8Hz' UNION ALL
SELECT 16, 4, N'Flash16Hz' UNION ALL
SELECT 17, 1, N'DevicePowerOff' UNION ALL
SELECT 17, 2, N'DeviceSwitchingOn' UNION ALL
SELECT 17, 3, N'DevicePowerOn' UNION ALL
SELECT 17, 4, N'DeviceSwitchingOff' UNION ALL
SELECT 17, 6, N'DeviceProhibited' UNION ALL
SELECT 18, 1, N'CameraFlashOff' UNION ALL
SELECT 18, 2, N'CameraFlashTorch' UNION ALL
SELECT 18, 3, N'CameraFlashPhoto' UNION ALL
SELECT 19, 1, N'DeviceSetPowerOff' UNION ALL
SELECT 19, 3, N'DeviceSetPowerOn' UNION ALL
SELECT 19, 5, N'DeviceSetDefault' UNION ALL
SELECT 19, 6, N'DeviceSetProhibited' UNION ALL
SELECT 20, 1, N'DockLinxAbsent' UNION ALL
SELECT 20, 2, N'DockLinxPresent' UNION ALL
SELECT 21, 1, N'DockLockLocked' UNION ALL
SELECT 21, 2, N'DockLockUnlocking' UNION ALL
SELECT 21, 3, N'DockLockUnlocked' UNION ALL
SELECT 21, 4, N'DockLockLocking' UNION ALL
SELECT 22, 1, N'DockUndockRequestPending' UNION ALL
SELECT 22, 2, N'DockUndocking' UNION ALL
SELECT 22, 3, N'DockUndocked' UNION ALL
SELECT 22, 4, N'DockDockedVehicle' UNION ALL
SELECT 22, 5, N'DockDockedDeskstation' UNION ALL
SELECT 22, 6, N'DockDockedMobileOffice' UNION ALL
SELECT 23, 1, N'DockPowerOff' UNION ALL
SELECT 23, 2, N'DockPowerOn' UNION ALL
SELECT 24, 1, N'LedRed' UNION ALL
SELECT 24, 2, N'LedYellow' UNION ALL
SELECT 24, 3, N'LedGreen' UNION ALL
SELECT 24, 4, N'LedBlue' UNION ALL
SELECT 25, 1, N'LedOff' UNION ALL
SELECT 25, 2, N'LedOn' UNION ALL
SELECT 25, 5, N'LedFlash' UNION ALL
SELECT 26, 1, N'LedPairBothOff' UNION ALL
SELECT 26, 2, N'LedPairColour1' UNION ALL
SELECT 26, 3, N'LedPairColour2' UNION ALL
SELECT 26, 4, N'LedPairBothOn' UNION ALL
SELECT 26, 5, N'LedPairColour1Flash' UNION ALL
SELECT 26, 6, N'LedPairColour2Flash' UNION ALL
SELECT 26, 7, N'LedPairBothFlash' UNION ALL
SELECT 26, 8, N'LedPairColour1Colour2Alternate' UNION ALL
SELECT 26, 9, N'LedPairColour1BothAlternate' UNION ALL
SELECT 26, 10, N'LedPairColour2BothAlternate' UNION ALL
SELECT 27, 1, N'TemperatureStateNormal' UNION ALL
SELECT 27, 2, N'TemperatureStateLow' UNION ALL
SELECT 27, 3, N'TemperatureStateWarn' UNION ALL
SELECT 27, 4, N'TemperatureStateHigh' UNION ALL
SELECT 28, 1, N'TemperatureLowThresholdDown' UNION ALL
SELECT 28, 2, N'TemperatureLowThresholdUp' UNION ALL
SELECT 28, 3, N'TemperatureVeryLowThresholdDown' UNION ALL
SELECT 28, 4, N'TemperatureVeryLowThresholdUp' UNION ALL
SELECT 28, 5, N'TemperatureHighThresholdDown' UNION ALL
SELECT 28, 6, N'TemperatureHighThresholdUp' UNION ALL
SELECT 31, 1, N'BatteryCharging' UNION ALL
SELECT 31, 2, N'BatteryFullyCharged' UNION ALL
SELECT 31, 3, N'BatteryPoweringSystem' UNION ALL
SELECT 31, 4, N'BatteryNotFitted'
COMMIT;
RAISERROR (N'[dbo].[Linxdatatypesvalues]: Loaded values', 10, 1) WITH NOWAIT;
GO
===================
then (drum roll..........)

When I run:-

=========================
SELECT 1 AS Tag,
null AS Parent,
types as [types!1!Types],
h.typesid as [typesid!2!Typesid],
H.name as [Name!2!Name],
H.Description as [Description!2!h.Description],
null,
null
FROM Linxdatatypes H
Union all

SELECT 2 AS Tag,
1 AS Parent,
h.types,
h.typesid,
null,
null,
V.value as [V.Value!3!Value],
V.Description as [V.Description!3!v.Description]
FROM Linxdatatypes H, Linxdatatypesvalues V
where H.typesid = V.typesid
ORDER BY 3,4,7
for xml explicit

============

I get:-
=========================================
Msg 6812, Level 16, State 1, Line 2
XML tag ID 2 that was originally declared as 'typesid' is being redeclared as 'Name'.============================================================

Whats going wrong?

Its driving me nuts....!!!

but when I run it without the "for XML" line - it seems ok..ish

Tag Parent types!1!Types typesid!2!Typesid Name!2!Name Description!2!h.Description (No column name) (No column name)
1 NULL EnumType 10 enumLightbarFlashingMode_t Lightbar settings| NULL NULL
2 1 EnumType 10 NULL NULL 0 FlashFast
2 1 EnumType 10 NULL NULL 1 FlashSlow
1 NULL EnumType 11 enumButtonStatus_t Push button status| NULL NULL
2 1 EnumType 11 NULL NULL 1 ButtonNotPushed
2 1 EnumType 11 NULL NULL 2 ButtonPushed
1 NULL EnumType 12 enumButtonPress_t Push button pushes| NULL NULL
2 1 EnumType 12 NULL NULL 1 ButtonMomentaryPush
2 1 EnumType 12 NULL NULL 2 ButtonPushAndHold
2 1 EnumType 12 NULL NULL 3 ButtonRelease
1 NULL EnumType 13 enumIgnitionSwitch_t State of ignition switch| NULL NULL
2 1 EnumType 13 NULL NULL 1 IgnitionSwitchOff
2 1 EnumType 13 NULL NULL 2 IgnitionSwitchOn
1 NULL EnumType 14 enumVehicleBatteryState_t Vehicle battery state| NULL NULL
2 1 EnumType 14 NULL NULL 1 VehicleBatteryStateNormal
2 1 EnumType 14 NULL NULL 2 VehicleBatteryStateLow
2 1 EnumType 14 NULL NULL 3 VehicleBatteryStateVeryLow
2 1 EnumType 14 NULL NULL 4 VehicleBatteryStateHigh
1 NULL EnumType 15 enumVehicleBatteryThreshold_t Threshold voltages for vehicle battery| NULL NULL
2 1 EnumType 15 NULL NULL 1 VehicleBatteryLowThresholdDown
2 1 EnumType 15 NULL NULL 2 VehicleBatteryLowThresholdUp
2 1 EnumType 15 NULL NULL 3 VehicleBatteryVeryLowThresholdDown
2 1 EnumType 15 NULL NULL 4 VehicleBatteryVeryLowThresholdUp
2 1 EnumType 15 NULL NULL 5 VehicleBatteryHighThresholdDown
2 1 EnumType 15 NULL NULL 6 VehicleBatteryHighThresholdUp
1 NULL EnumType 16 enumLightbarAdvancedFlashingMode_t Lightbar settings| NULL NULL
2 1 EnumType 16 NULL NULL 0 Flash1Hz
2 1 EnumType 16 NULL NULL 1 Flash2Hz
2 1 EnumType 16 NULL NULL 2 Flash4Hz
2 1 EnumType 16 NULL NULL 3 Flash8Hz
2 1 EnumType 16 NULL NULL 4 Flash16Hz
1 NULL EnumType 17 enumDeviceState_t State of device| NULL NULL
2 1 EnumType 17 NULL NULL 1 DevicePowerOff
2 1 EnumType 17 NULL NULL 2 DeviceSwitchingOn
2 1 EnumType 17 NULL NULL 3 DevicePowerOn
2 1 EnumType 17 NULL NULL 4 DeviceSwitchingOff
2 1 EnumType 17 NULL NULL 6 DeviceProhibited
1 NULL EnumType 18 enumCameraFlashState_t Camera flash state| NULL NULL
2 1 EnumType 18 NULL NULL 1 CameraFlashOff
2 1 EnumType 18 NULL NULL 2 CameraFlashTorch
2 1 EnumType 18 NULL NULL 3 CameraFlashPhoto
1 NULL EnumType 19 enumDeviceRequiredState_t Required state of device| NULL NULL
2 1 EnumType 19 NULL NULL 1 DeviceSetPowerOff
2 1 EnumType 19 NULL NULL 3 DeviceSetPowerOn
2 1 EnumType 19 NULL NULL 5 DeviceSetDefault
2 1 EnumType 19 NULL NULL 6 DeviceSetProhibited
1 NULL EnumType 20 enumDockLinxPresenceState_t LINX present in dock state| NULL NULL
2 1 EnumType 20 NULL NULL 1 DockLinxAbsent
2 1 EnumType 20 NULL NULL 2 DockLinxPresent
1 NULL EnumType 21 enumDockLockStatus_t Status of docking station lock| NULL NULL
2 1 EnumType 21 NULL NULL 1 DockLockLocked
2 1 EnumType 21 NULL NULL 2 DockLockUnlocking
2 1 EnumType 21 NULL NULL 3 DockLockUnlocked
2 1 EnumType 21 NULL NULL 4 DockLockLocking
1 NULL EnumType 22 enumDockStatus_t Status of docking| NULL NULL
2 1 EnumType 22 NULL NULL 1 DockUndockRequestPending
2 1 EnumType 22 NULL NULL 2 DockUndocking
2 1 EnumType 22 NULL NULL 3 DockUndocked
2 1 EnumType 22 NULL NULL 4 DockDockedVehicle
2 1 EnumType 22 NULL NULL 5 DockDockedDeskstation
2 1 EnumType 22 NULL NULL 6 DockDockedMobileOffice
1 NULL EnumType 23 enumDockPowerStatus_t Power state in docking station| NULL NULL
2 1 EnumType 23 NULL NULL 1 DockPowerOff
2 1 EnumType 23 NULL NULL 2 DockPowerOn
1 NULL EnumType 24 enumLedColour_t Colour of LED| NULL NULL
2 1 EnumType 24 NULL NULL 1 LedRed
2 1 EnumType 24 NULL NULL 2 LedYellow
2 1 EnumType 24 NULL NULL 3 LedGreen
2 1 EnumType 24 NULL NULL 4 LedBlue
1 NULL EnumType 25 enumLedState_t On, off or flashing state of LED| NULL NULL
2 1 EnumType 25 NULL NULL 1 LedOff
2 1 EnumType 25 NULL NULL 2 LedOn
2 1 EnumType 25 NULL NULL 5 LedFlash
1 NULL EnumType 26 enumLedPairState_t On, off or flashing state of LED| NULL NULL
2 1 EnumType 26 NULL NULL 1 LedPairBothOff
2 1 EnumType 26 NULL NULL 2 LedPairColour1
2 1 EnumType 26 NULL NULL 3 LedPairColour2
2 1 EnumType 26 NULL NULL 4 LedPairBothOn
2 1 EnumType 26 NULL NULL 5 LedPairColour1Flash
2 1 EnumType 26 NULL NULL 6 LedPairColour2Flash
2 1 EnumType 26 NULL NULL 7 LedPairBothFlash
2 1 EnumType 26 NULL NULL 8 LedPairColour1Colour2Alternate
2 1 EnumType 26 NULL NULL 9 LedPairColour1BothAlternate
2 1 EnumType 26 NULL NULL 10 LedPairColour2BothAlternate
1 NULL EnumType 27 enumTemperatureState_t LINX thermal state| NULL NULL
2 1 EnumType 27 NULL NULL 1 TemperatureStateNormal
2 1 EnumType 27 NULL NULL 2 TemperatureStateLow
2 1 EnumType 27 NULL NULL 3 TemperatureStateWarn
2 1 EnumType 27 NULL NULL 4 TemperatureStateHigh
1 NULL EnumType 28 enumTemperatureThreshold_t Threshold temperatures for LINX| NULL NULL
2 1 EnumType 28 NULL NULL 1 TemperatureLowThresholdDown
2 1 EnumType 28 NULL NULL 2 TemperatureLowThresholdUp
2 1 EnumType 28 NULL NULL 3 TemperatureVeryLowThresholdDown
2 1 EnumType 28 NULL NULL 4 TemperatureVeryLowThresholdUp
2 1 EnumType 28 NULL NULL 5 TemperatureHighThresholdDown
2 1 EnumType 28 NULL NULL 6 TemperatureHighThresholdUp
1 NULL EnumType 31 enumBatteryState_t Battery state| NULL NULL
2 1 EnumType 31 NULL NULL 1 BatteryCharging
2 1 EnumType 31 NULL NULL 2 BatteryFullyCharged
2 1 EnumType 31 NULL NULL 3 BatteryPoweringSystem
2 1 EnumType 31 NULL NULL 4 BatteryNotFitted
1 NULL NumericType 0 U32_t Unsigned 32-bit value NULL NULL
1 NULL NumericType 1 U16_t Unsigned 16-bit value NULL NULL
1 NULL NumericType 2 U8_t Unsigned 8-bit value NULL NULL
1 NULL NumericType 3 s32_t Signed 32-bit value NULL NULL
1 NULL NumericType 4 s16_t Signed 16-bit value NULL NULL
1 NULL NumericType 5 s8_t Signed 8-bit value NULL NULL
1 NULL NumericType 6 boolean_t Boolean value NULL NULL
1 NULL NumericType 7 extendedData_t Extended data pointer NULL NULL
1 NULL NumericType 8 voltage_t Volts in tenths of a Volt NULL NULL
1 NULL NumericType 9 test_t Test data type NULL NULL
1 NULL NumericType 29 temperature_t Temperature in tenths of a degree Celsius NULL NULL
1 NULL NumericType 30 batteryLevel_t Battery charge level percentage NULL NULL

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-28 : 21:16:07

First of all, have you tried FOR XML PATH? I'd really recommend it as it is much simpler than using FOR XML EXPLICIT and I have not yet had a case where I couldn't do what I needed with FOR XML PATH.

You did a great job of putting everything into your question with the sample code and table and so on, unfortunately you left out one key thing - the structure of the XML you're trying to get to. So, I can tell you what you're doing wrong but I can't tell you how to get to exactly what you want without the XML structure.

When you use FOR XML EXPLICIT, the column names determine the XML structure in this format TAGNAME!TAGID!ATTRIBUTENAME - so the problem with your code is that you are trying to use different names for the same tag (tag 2 in this case). You first name a column [typesid!2!Typesid] meaning that tag 2 is named typesid and then you name another column [Name!2!Name] meaning you are renaming tag 2 as Name.

Also, you have to name columns in the first query when using a UNION, so you need to move all the names to the first query or you'll get more errors. Here's a working version of your query (it probably won't give the XML you want, but hopefully it will at least help you get further, and if you post the XML structure I can help you get there).

SELECT 1 AS Tag, 
null AS Parent,
types as [types!1!Types],
h.typesid as [typesid!2!Typesid],
H.name as [typesid!2!Name],
H.Description as [typesid!2!h.Description],
null as [V.Value!3!Value],
null as [V.Value!3!v.Description]
FROM Linxdatatypes H 
Union all

SELECT 2, 
1,
h.types,
h.typesid,
null,
null,
V.value,
V.Description 
FROM Linxdatatypes H, Linxdatatypesvalues V 
where H.typesid = V.typesid 
ORDER BY 3,4,7
for xml explicit

NicJ
Starting Member

15 Posts

Posted - 2009-05-29 : 04:10:17

Thanks for the response, I tried to give someone a chance to replicate my problem.

The actual XML I will be creating is far bigger and more complex with over 10 tables in all - however I want to prove the theory before making things too complex.

I have examined many samples of the use of 'explicit' on the net and never spotted that the first element was a unique name for the entire tag level - that explains a lot....duh!!!

I will now try the new format once I can get back onto the VPN server and try again.

Cheers

Nic

NicJ
Starting Member

15 Posts

Posted - 2009-05-29 : 04:49:11

Well the error message has gone - now I need the next bit of help you mentioned.

This is the format I am failing to acheive:-

<LinxDataTypes>
<types:NumericType PK_LinxTypeID="0" Name="U32_t" BasicType="unsigned long">
<types:Description>Unsigned 32-bit value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="1" Name="U16_t" BasicType="unsigned short">
<types:Description>Unsigned 16-bit value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="2" Name="U8_t" BasicType="unsigned char">
<types:Description>Unsigned 8-bit value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="3" Name="S32_t" BasicType="signed long">
<types:Description>Signed 32-bit value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="4" Name="S16_t" BasicType="signed short">
<types:Description>Signed 16-bit value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="5" Name="S8_t" BasicType="signed char">
<types:Description>Signed 8-bit value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="6" Name="boolean_t" BasicType="unsigned long">
<types:Description>Boolean value</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="7" Name="extendedData_t" BasicType="unsigned char*">
<types:Description>Extended data pointer</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="8" Name="voltage_t" FK_Alias="0" DecimalPlaces="1">
<types:Description>Volts in tenths of a Volt</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="9" Name="test_t" FK_Alias="0">
<types:Description>Test data type</types:Description>
</types:NumericType>
<types:EnumType PK_LinxTypeID="10" Name="enumLightbarFlashingMode_t">
<types:Description>Lightbar settings</types:Description>
<types:EnumValue PK_Value="0">FlashFast</types:EnumValue>
<types:EnumValue PK_Value="1">FlashSlow</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="11" Name="enumButtonStatus_t">
<types:Description>Push button status</types:Description>
<types:EnumValue PK_Value="1">ButtonNotPushed</types:EnumValue>
<types:EnumValue PK_Value="2">ButtonPushed</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="12" Name="enumButtonPress_t">
<types:Description>Push button pushes</types:Description>
<types:EnumValue PK_Value="1">ButtonMomentaryPush</types:EnumValue>
<types:EnumValue PK_Value="2">ButtonPushAndHold</types:EnumValue>
<types:EnumValue PK_Value="3">ButtonRelease</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="13" Name="enumIgnitionSwitch_t">
<types:Description>State of ignition switch</types:Description>
<types:EnumValue PK_Value="1">IgnitionSwitchOff</types:EnumValue>
<types:EnumValue PK_Value="2">IgnitionSwitchOn</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="14" Name="enumVehicleBatteryState_t">
<types:Description>Vehicle battery state</types:Description>
<types:EnumValue PK_Value="1">VehicleBatteryStateNormal</types:EnumValue>
<types:EnumValue PK_Value="2">VehicleBatteryStateLow</types:EnumValue>
<types:EnumValue PK_Value="3">VehicleBatteryStateVeryLow</types:EnumValue>
<types:EnumValue PK_Value="4">VehicleBatteryStateHigh</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="15" Name="enumVehicleBatteryThreshold_t">
<types:Description>Threshold voltages for vehicle battery</types:Description>
<types:EnumValue PK_Value="1">VehicleBatteryLowThresholdDown</types:EnumValue>
<types:EnumValue PK_Value="2">VehicleBatteryLowThresholdUp</types:EnumValue>
<types:EnumValue PK_Value="3">VehicleBatteryVeryLowThresholdDown</types:EnumValue>
<types:EnumValue PK_Value="4">VehicleBatteryVeryLowThresholdUp</types:EnumValue>
<types:EnumValue PK_Value="5">VehicleBatteryHighThresholdDown</types:EnumValue>
<types:EnumValue PK_Value="6">VehicleBatteryHighThresholdUp</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="16" Name="enumLightbarAdvancedFlashingMode_t">
<types:Description>Lightbar settings</types:Description>
<types:EnumValue PK_Value="0">Flash1Hz</types:EnumValue>
<types:EnumValue PK_Value="1">Flash2Hz</types:EnumValue>
<types:EnumValue PK_Value="2">Flash4Hz</types:EnumValue>
<types:EnumValue PK_Value="3">Flash8Hz</types:EnumValue>
<types:EnumValue PK_Value="4">Flash16Hz</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="17" Name="enumDeviceState_t">
<types:Description>State of device</types:Description>
<types:EnumValue PK_Value="1">DevicePowerOff</types:EnumValue>
<types:EnumValue PK_Value="2">DeviceSwitchingOn</types:EnumValue>
<types:EnumValue PK_Value="3">DevicePowerOn</types:EnumValue>
<types:EnumValue PK_Value="4">DeviceSwitchingOff</types:EnumValue>
<types:EnumValue PK_Value="6">DeviceProhibited</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="18" Name="enumCameraFlashState_t">
<types:Description>Camera flash state</types:Description>
<types:EnumValue PK_Value="1">CameraFlashOff</types:EnumValue>
<types:EnumValue PK_Value="2">CameraFlashTorch</types:EnumValue>
<types:EnumValue PK_Value="3">CameraFlashPhoto</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="19" Name="enumDeviceRequiredState_t">
<types:Description>Required state of device</types:Description>
<types:EnumValue PK_Value="1">DeviceSetPowerOff</types:EnumValue>
<types:EnumValue PK_Value="3">DeviceSetPowerOn</types:EnumValue>
<types:EnumValue PK_Value="5">DeviceSetDefault</types:EnumValue>
<types:EnumValue PK_Value="6">DeviceSetProhibited</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="20" Name="enumDockLinxPresenceState_t">
<types:Description>LINX present in dock state</types:Description>
<types:EnumValue PK_Value="1">DockLinxAbsent</types:EnumValue>
<types:EnumValue PK_Value="2">DockLinxPresent</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="21" Name="enumDockLockStatus_t">
<types:Description>Status of docking station lock</types:Description>
<types:EnumValue PK_Value="1">DockLockLocked</types:EnumValue>
<types:EnumValue PK_Value="2">DockLockUnlocking</types:EnumValue>
<types:EnumValue PK_Value="3">DockLockUnlocked</types:EnumValue>
<types:EnumValue PK_Value="4">DockLockLocking</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="22" Name="enumDockStatus_t">
<types:Description>Status of docking</types:Description>
<types:EnumValue PK_Value="1">DockUndockRequestPending</types:EnumValue>
<types:EnumValue PK_Value="2">DockUndocking</types:EnumValue>
<types:EnumValue PK_Value="3">DockUndocked</types:EnumValue>
<types:EnumValue PK_Value="4">DockDockedVehicle</types:EnumValue>
<types:EnumValue PK_Value="5">DockDockedDeskstation</types:EnumValue>
<types:EnumValue PK_Value="6">DockDockedMobileOffice</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="23" Name="enumDockPowerStatus_t">
<types:Description>Power state in docking station</types:Description>
<types:EnumValue PK_Value="1">DockPowerOff</types:EnumValue>
<types:EnumValue PK_Value="2">DockPowerOn</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="24" Name="enumLedColour_t">
<types:Description>Colour of LED</types:Description>
<types:EnumValue PK_Value="1">LedRed</types:EnumValue>
<types:EnumValue PK_Value="2">LedYellow</types:EnumValue>
<types:EnumValue PK_Value="3">LedGreen</types:EnumValue>
<types:EnumValue PK_Value="4">LedBlue</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="25" Name="enumLedState_t">
<types:Description>On, off or flashing state of LED</types:Description>
<types:EnumValue PK_Value="1">LedOff</types:EnumValue>
<types:EnumValue PK_Value="2">LedOn</types:EnumValue>
<types:EnumValue PK_Value="5">LedFlash</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="26" Name="enumLedPairState_t">
<types:Description>On, off or flashing state of LED</types:Description>
<types:EnumValue PK_Value="1">LedPairBothOff</types:EnumValue>
<types:EnumValue PK_Value="2">LedPairColour1</types:EnumValue>
<types:EnumValue PK_Value="3">LedPairColour2</types:EnumValue>
<types:EnumValue PK_Value="4">LedPairBothOn</types:EnumValue>
<types:EnumValue PK_Value="5">LedPairColour1Flash</types:EnumValue>
<types:EnumValue PK_Value="6">LedPairColour2Flash</types:EnumValue>
<types:EnumValue PK_Value="7">LedPairBothFlash</types:EnumValue>
<types:EnumValue PK_Value="8">LedPairColour1Colour2Alternate</types:EnumValue>
<types:EnumValue PK_Value="9">LedPairColour1BothAlternate</types:EnumValue>
<types:EnumValue PK_Value="10">LedPairColour2BothAlternate</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="27" Name="enumTemperatureState_t">
<types:Description>LINX thermal state</types:Description>
<types:EnumValue PK_Value="1">TemperatureStateNormal</types:EnumValue>
<types:EnumValue PK_Value="2">TemperatureStateLow</types:EnumValue>
<types:EnumValue PK_Value="3">TemperatureStateWarn</types:EnumValue>
<types:EnumValue PK_Value="4">TemperatureStateHigh</types:EnumValue>
</types:EnumType>
<types:EnumType PK_LinxTypeID="28" Name="enumTemperatureThreshold_t">
<types:Description>Threshold temperatures for LINX</types:Description>
<types:EnumValue PK_Value="1">TemperatureLowThresholdDown</types:EnumValue>
<types:EnumValue PK_Value="2">TemperatureLowThresholdUp</types:EnumValue>
<types:EnumValue PK_Value="3">TemperatureVeryLowThresholdDown</types:EnumValue>
<types:EnumValue PK_Value="4">TemperatureVeryLowThresholdUp</types:EnumValue>
<types:EnumValue PK_Value="5">TemperatureHighThresholdDown</types:EnumValue>
<types:EnumValue PK_Value="6">TemperatureHighThresholdUp</types:EnumValue>
</types:EnumType>
<types:NumericType PK_LinxTypeID="29" Name="temperature_t" FK_Alias="3" DecimalPlaces="1">
<types:Description>Temperature in tenths of a degree Celsius</types:Description>
</types:NumericType>
<types:NumericType PK_LinxTypeID="30" Name="batteryLevel_t" FK_Alias="0" DecimalPlaces="0">
<types:Description>Battery charge level percentage</types:Description>
</types:NumericType>
<types:EnumType PK_LinxTypeID="31" Name="enumBatteryState_t">
<types:Description>Battery state</types:Description>
<types:EnumValue PK_Value="1">BatteryCharging</types:EnumValue>
<types:EnumValue PK_Value="2">BatteryFullyCharged</types:EnumValue>
<types:EnumValue PK_Value="3">BatteryPoweringSystem</types:EnumValue>
<types:EnumValue PK_Value="4">BatteryNotFitted</types:EnumValue>
</types:EnumType>
</LinxDataTypes>

Any helpwould be much appreciated - I am not new to sql server or SQL itself - but XML is a new black art for me I am afraid.

Cheers

Nic

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-05-30 : 01:48:57

You're using a namespace in the XML (the types:) prefix on the elements and I'm not even sure how you use namespaces with FOR EXPLICIT. You can put the types: prefix in the tag names but the actual namespace declarations don't come through. Any how FOR XML PATH supports namespaces directly and as I said before I find it much easier to work with.

Here's a query that uses FOR XML PATH to give you pretty much what you're looking for:

WITH XMLNAMESPACES ('uri' AS types)
SELECT 
	(SELECT 
	h.typesid AS [@PK_LinxTypeID],
	H.name AS [@Name],
	H.Basictype AS [@BasicType],
	H.Description AS [types:Description]
	FROM Linxdatatypes H 
	WHERE types = 'NumericType'
	FOR XML PATH('types:NumericType'), TYPE),
	(SELECT 
	h.typesid AS [@PK_LinxTypeID],
	H.name AS [@Name],
	H.Basictype AS [@BasicType],
	H.Description AS [types:Description],
	(SELECT V.VALUE AS [@PK_Value],
		V.Description AS [text()]
		FROM Linxdatatypesvalues V
		WHERE H.typesid = V.typesid
		FOR XML PATH('types:EnumValue'), TYPE)
	FROM Linxdatatypes H
	WHERE types = 'EnumType'
	FOR XML PATH('types:EnumType'), TYPE)
FOR XML PATH('LinxDataTypes')

NicJ
Starting Member

15 Posts

Posted - 2009-06-01 : 05:48:32

Thanks for that - the response was far more detailed than I had expected.

I have not used XML in anger at all and I dont know if the inclusion of the extra:-

xmlns:types="uri"

in each line will cause a problem - I will ask the guys who are expecting the data.

Now all I need to do is spend a few hours understanding how you acheived that result so I can replicate the technique for the other XML 'extractions'.

Cheers

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources