For XML Explicit

Hello,
Suppose this table:
Master_Plan
(Master_Plan_Id int,
Community_Id int,
County_Id int,
Market_Id int,
Location_Description varchar(127) )
I would like to have the XML presented as follows:
<Company>
<Data>
<ProjectData
Master_Plan_Id="1">
<Community_Id
valueid="1792"/>
<County_Id
valueid="12"/>
<Market_Id
valueid="2"/>
<Location_Description
value="This Is A Test"/>
</ProjectData>
</Data>
</Company>
The following query returns the result without the sub-
elements "valueid" - data is one level "flatter"
SELECT 1 as Tag
,NULLas Parent
,Master_Plan_Idas [ProjectData!1!
Master_Plan_Id]
,Community_Idas [ProjectData!1!
Community_Id!element]
,County_Idas [ProjectData!1!
County_Id!element]
,Market_Idas [ProjectData!1!
Market_Id!element]
,Location_Descriptionas [ProjectData!1!
Location_Description!element]
FROM Master_Plan
FOR XML EXPLICIT
How can I code the query to present the values as sub-
elements of the corresponding column as explained above?
Thanks!
You need a UNION for each new tag - here's my (not very elegant) solution -
someone else out there might have some better ideas!
SELECT 1 as Tag ,NULL as Parent
,Master_Plan_Id as [ProjectData!1!Master_Plan_Id]
,NULL as [Community_id!2!Valueid]
,NULL as [County_id!3!Valueid]
,NULL as [Market_id!4!Valueid]
,NULL as [Location_Description!5!Valueid]
FROM Master_Plan
UNION ALL
SELECT 2, 1,
Master_Plan_Id,
Community_Id,
NULL,
NULL,
NULL
FROM Master_Plan
UNION ALL
SELECT 3, 1,
Master_Plan_Id,
NULL,
County_Id,
NULL,
NULL
FROM Master_Plan
UNION ALL
SELECT 4, 1,
Master_Plan_Id,
NULL,
NULL,
Market_Id,
NULL
FROM Master_Plan
UNION ALL
SELECT 5, 1,
Master_Plan_Id,
NULL,
NULL,
NULL,
Location_Description
FROM Master_Plan
ORDER BY [ProjectData!1!Master_Plan_Id],
[Location_Description!5!Valueid],
[Market_Id!4!Valueid],
[County_id!3!Valueid],
[Community_Id!2!Valueid]
FOR XML EXPLICIT
Hope that helps!
G
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:1c44d01c421f4$e9d6ecd0$a301280a@.phx.gbl...
> Hello,
> Suppose this table:
> Master_Plan
> (Master_Plan_Id int,
> Community_Id int,
> County_Id int,
> Market_Id int,
> Location_Description varchar(127) )
> I would like to have the XML presented as follows:
> <Company>
> <Data>
> <ProjectData
> Master_Plan_Id="1">
> <Community_Id
> valueid="1792"/>
> <County_Id
> valueid="12"/>
> <Market_Id
> valueid="2"/>
> <Location_Description
> value="This Is A Test"/>
> </ProjectData>
> </Data>
> </Company>
>
> The following query returns the result without the sub-
> elements "valueid" - data is one level "flatter"
> SELECT 1 as Tag
> ,NULL as Parent
> ,Master_Plan_Id as [ProjectData!1!
> Master_Plan_Id]
> ,Community_Id as [ProjectData!1!
> Community_Id!element]
> ,County_Id as [ProjectData!1!
> County_Id!element]
> ,Market_Id as [ProjectData!1!
> Market_Id!element]
> ,Location_Description as [ProjectData!1!
> Location_Description!element]
> FROM Master_Plan
> FOR XML EXPLICIT
> How can I code the query to present the values as sub-
> elements of the corresponding column as explained above?
> Thanks!
|||Instead of giving you the ugly FOR XML explicit query (see Graeme's post), I
would like to know why you want to use attributes to represent the value of
the element. This is in my opinion adding too much complexity to your XML
format (at least based on what you have presented here).
If you really need this format, the following will be the more elegant FOR
XML PATH query possible in Yukon...
SELECT Master_Plan_Id as [ProjectData/@.Master_Plan_Id]
,Community_Id as [ProjectData/Community_Id/@.valueid]
,County_Id as [ProjectData/County_Id/@.valueid]
,Market_Id as [ProjectData/Market_Id/@.valueid]
,Location_Description as [ProjectData/Location_Description/@.value]
FROM Master_Plan
FOR XML PATH('Data'), ROOT('Company')
However, I really would recommend using a format that makes the values
element content instead of a value of a "value" attribute.
Best regards
Michael
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:1c44d01c421f4$e9d6ecd0$a301280a@.phx.gbl...
> Hello,
> Suppose this table:
> Master_Plan
> (Master_Plan_Id int,
> Community_Id int,
> County_Id int,
> Market_Id int,
> Location_Description varchar(127) )
> I would like to have the XML presented as follows:
> <Company>
> <Data>
> <ProjectData
> Master_Plan_Id="1">
> <Community_Id
> valueid="1792"/>
> <County_Id
> valueid="12"/>
> <Market_Id
> valueid="2"/>
> <Location_Description
> value="This Is A Test"/>
> </ProjectData>
> </Data>
> </Company>
>
> The following query returns the result without the sub-
> elements "valueid" - data is one level "flatter"
> SELECT 1 as Tag
> ,NULL as Parent
> ,Master_Plan_Id as [ProjectData!1!
> Master_Plan_Id]
> ,Community_Id as [ProjectData!1!
> Community_Id!element]
> ,County_Id as [ProjectData!1!
> County_Id!element]
> ,Market_Id as [ProjectData!1!
> Market_Id!element]
> ,Location_Description as [ProjectData!1!
> Location_Description!element]
> FROM Master_Plan
> FOR XML EXPLICIT
> How can I code the query to present the values as sub-
> elements of the corresponding column as explained above?
> Thanks!
|||Hi Michael,
The XML will be used to populate data entry forms, and
will contain other attributes for each element that were
not included in the example. It will also be used in
reverse to populate a set of tables with a subset of the
attributes (which were included in the example).
It looks like the Yukon features will do exactly what I
need - which looks exactly like the OpenXML I'm generating
to populate the tables.
I guess I'll home-grow a solution until the features are
available.
Thanks for the response.
Kevin

>--Original Message--
>Instead of giving you the ugly FOR XML explicit query
(see Graeme's post), I
>would like to know why you want to use attributes to
represent the value of
>the element. This is in my opinion adding too much
complexity to your XML
>format (at least based on what you have presented here).
>If you really need this format, the following will be the
more elegant FOR
>XML PATH query possible in Yukon...
>SELECT Master_Plan_Id as [ProjectData/@.Master_Plan_Id]
>,Community_Id as [ProjectData/Community_Id/@.valueid]
>,County_Id as [ProjectData/County_Id/@.valueid]
>,Market_Id as [ProjectData/Market_Id/@.valueid]
>,Location_Description as
[ProjectData/Location_Description/@.value]
>FROM Master_Plan
>FOR XML PATH('Data'), ROOT('Company')
>However, I really would recommend using a format that
makes the values
>element content instead of a value of a "value" attribute.
>Best regards
>Michael
>"Kevin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c44d01c421f4$e9d6ecd0$a301280a@.phx.gbl...
>
>.
>