SQL Create XML from a query without the Repeating of the tags

Question

Im trying to write an sql query which generates xml and im nearly there but i need to be able to stop it from repeating the tags.

This is the query i am currently using

select DISTINCT
  objecttype.Type 
, object.text
, objectelement.Value
, objectlanguage.CultureInfo
, isnull(objectelementtext.ElementText, objectelement.Value)
from [object] object
    inner join [objecttype] objecttype
        on object.ObjectTypeID = objecttype.ObjectTypeID
    inner join [ObjectElement] objectelement
        on object.ObjectID = objectelement.ObjectID
    outer APPLY 
        (
            select objectlanguage.ObjectLanguageID,    objectlanguage.CultureInfo
                from [ObjectLanguage] objectlanguage
                where active = 1
        ) objectlanguage
    left join [ObjectElementText] objectelementtext
        on objectelementtext.ObjectElementID = objectelement.ObjectElementID
            and objectlanguage.ObjectLanguageID = objectelementtext.ObjectLanguageID
FOR XML AUTO, ELEMENTS, ROOT('Translations')

So this generates the XML but it looks like the following.

    <Translations>
      <objecttype>
        <Type>Report</Type>
        <object>
  <text>TrayList</text>
  <objectelement>
    <Value>***** For Information Only *****</Value>
    <objectlanguage>
      <CultureInfo>en-gb</CultureInfo>***** For Information Only *****</objectlanguage>
    <objectlanguage>
      <CultureInfo>en-tt</CultureInfo>******** For Information Only *****</objectlanguage>
    <objectlanguage>
      <CultureInfo>en-us</CultureInfo>***** For Information Only *****</objectlanguage>
    <objectlanguage>
      <CultureInfo>it-it</CultureInfo>***** Solo Per Informazione *****</objectlanguage>
    <objectlanguage>
      <CultureInfo>zh-cn</CultureInfo>***** For Information Only *****</objectlanguage>
  </objectelement>

What im trying to do is stop it from repeating the opening tags, so for example

    <objectlanguage>
        <CultureInfo ci="en-gb" text ="***** For Information Only*****"/>
        <CultureInfo ci="en-it" text ="***** For Information Only*****"/>
        <CultureInfo ci="en-us" text ="***** For Information Only*****"/>
        <CultureInfo ci="zn-cn" text ="***** For Information Only*****"/>
        <CultureInfo ci="it-tt" text ="***** For Information Only*****"/>
    </objectlanguage>

Any help with this would be very much appreciated please, i think i am on the right lines but i need to tweak my query some how to tell it to not repeat them but i am unsure how.

Answer 1

Just to give everyone an update i have completed the script using xml explicit and it works perfectly.

The script i use if it helps anybody else is as follow

select 
DISTINCT
  1 AS TAG
, NULL AS PARENT, 
  objecttype.Type as [ObjectType!1!Text]
, NULL as [Object!2!Text]                                                           
, NULL as [ObjectElement!3!ObjectRef]                                                       
, NULL as [ObjectElement!3!Text]                                                
, NULL as [ObjectElementText!4!Culture]                                             
, NULL as [ObjectElementText!4!Text]        
from [object] object
    inner join [objecttype] objecttype
        on object.ObjectTypeID = objecttype.ObjectTypeID

UNION ALL

select
DISTINCT 
  2 AS TAG
, 1 AS PARENT, 
  objecttype.Type                                                           
, object.text
, NULL                                                      
, NULL                                          
, NULL                                          
, NULL                                                              
from [object] object
    inner join [objecttype] objecttype
        on object.ObjectTypeID = objecttype.ObjectTypeID

UNION ALL


select 
DISTINCT
  3 AS TAG
, 2 AS PARENT, 
  objecttype.Type as ObjectType                                                     
, object.text as [Object]                                                           
, objectelement.ObjectRef as [ObjectRef]
, objectelement.Value as [ObjectElement]                                                
, NULL
, NULL
from [object] object
    inner join [objecttype] objecttype
        on object.ObjectTypeID = objecttype.ObjectTypeID
    inner join [ObjectElement] objectelement
        on object.ObjectID = objectelement.ObjectID

UNION ALL

SELECT
DISTINCT 
  4 AS TAG
, 3 AS PARENT, 
  objecttype.Type as ObjectType                                                         
, object.text as [Object]   
, objectelement.ObjectRef as [ObjectRef]                                                        
, objectelement.Value as [ObjectElement]                                                
, objectlanguage.CultureInfo as [Culture]                                               
, isnull(objectelementtext.ElementText, objectelement.Value) as [ObjectElementText]     
from [object] object
    inner join [objecttype] objecttype
        on object.ObjectTypeID = objecttype.ObjectTypeID
    inner join [ObjectElement] objectelement
        on object.ObjectID = objectelement.ObjectID
    outer APPLY 
        (
            select objectlanguage.ObjectLanguageID, objectlanguage.CultureInfo
                from [ObjectLanguage] objectlanguage
                where active = 1
        ) objectlanguage
    left join [ObjectElementText] objectelementtext
        on objectelementtext.ObjectElementID = objectelement.ObjectElementID
            and objectlanguage.ObjectLanguageID = objectelementtext.ObjectLanguageID
ORDER BY 
  [ObjectType!1!Text]
, [Object!2!Text]
, [ObjectElement!3!ObjectRef]
, [ObjectElement!3!Text]
, [ObjectElementText!4!Culture]
, [ObjectElementText!4!Text]
FOR XML EXPLICIT, ROOT('Translations')

Answer 2

Your expected output is not valid XML. There is no <SomeElement="content"/> ...

Look at this example:

DECLARE @dummy TABLE(CultureInfo VARCHAR(100),SomeText VARCHAR(100));
INSERT INTO @dummy VALUES ('en-gb','Today is Friday')
                         ,('de-de','Heute ist Freitag');
SELECT CultureInfo AS [@ci]
      ,SomeText AS [@text] 
FROM @dummy
FOR XML PATH('CultureInfo'),ROOT('objectlanguage');

The result

<objectlanguage>
  <CultureInfo ci="en-gb" text="Today is Friday" />
  <CultureInfo ci="de-de" text="Heute ist Freitag" />
</objectlanguage>

You should prefer FOR XML PATH as it allows the most influence on the final structure. With FOR XML AUTO you let other people decide for you... And with ELEMENTS you force the engine to put your content in elements and not in attributes...

UPDATE

This is a blind flight, but you might try something like this

  select DISTINCT
  objecttype.Type 
, object.text
, (
    SELECT CultureInfo AS [@ci]
          ,SomeText AS [@text] 
    FROM ObjectLanguage
    WHERE active=1 AND ObjectLanguage.ObjectLanguageID=ObjectElement.ObjectLanguageID
    FOR XML PATH('CultureInfo'),ROOT('objectlanguage'),TYPE
  )
  --, more columns
  FROM ... the rest without objectLanguage