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.

`<CultureInfo = "en-gb" text ="***** For Information Only*****"/>' is not valid XML. Correct please. — Serg
– Serg, Commented Mar 31, 2017 at 15:51
it might not be true 100% formatting, but its how we require it for an importer that we have — Stew
– Stew, Commented Mar 31, 2017 at 15:56
but is is how we require it - I truly doubt this! This is not valid XML and will not be readable with standard tools... — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 31, 2017 at 16:00

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 31, 2017 at 15:54

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stew Over a year ago

Your example gives me a good grounds to work on, i just need to work out how to incorporate the other elements like the objecttype, object, objectelement, language and then the objectelementtext.

Stew Over a year ago

Cheers, doesnt quiet work, i will have to play around. The problem is i need the languageid to join over to the objectelementtext table. I would normally join to objectelementtext first and then onto language but i need to be able to return a language row as english by default if a row doesnt exist in objectelementtext for that language. If you see my isnull. I will keep playing around as it points me in the right direction atleast

Gottfried Lesigang Over a year ago

I'm sure we could help you, but please read How to ask a good SQL question and How to create a MCVE. Try to create a reduced stand-alone sample with input data and expected output.

Gottfried Lesigang Over a year ago

@Stew Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Since you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding!

Stew · Accepted Answer · 2017-04-04 08:01:29Z

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')

Collectives™ on Stack Overflow

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

2 Answers 2

UPDATE

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related