Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Error when using RETURN XML AUTO in some SWQL queries

When issuing a query that joins 2 tables and selects columns from both, and using RETURN XML AUTO, I get this error in our app as well as SWQL studio: More than one root element in the generated schema - [status,definitions]

Here is an example query that causes this:

SELECT TOP 10 status.AlertDefID AS AlertID, definitions.Name as AlertDef FROM Orion.AlertStatus status LEFT JOIN Orion.AlertDefinitions definitions ON definitions.AlertDefID = status.AlertDefID ORDER BY TriggerTimeStamp DESC RETURN XML AUTO

Using RETURN XML RAW works but it names the XML elements as c0, c1 which is a bit inconvenient when parsing out the data. Any tips on fixing this or a better approach?

- <s:Fault xmlns:s="http://www.w3.org/2003/05/soap-envelope">
- <s:Code>
 <s:Value>s:Receiver</s:Value>
- <s:Subcode>
 <s:Value xmlns:a="http://schemas.microsoft.com/net/2005/12/windowscommunicationfoundation/dispatcher">a:InternalServiceFault</s:Value>
 </s:Subcode>
 </s:Code>
- <s:Reason>
 <s:Text xml:lang="">More than one root element in the generated schema - [status,definitions]</s:Text>
 </s:Reason>
- <s:Detail>
- <ExceptionDetail xmlns="http://schemas.datacontract.org/2004/07/System.ServiceModel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
 <HelpLink i:nil="true" />
 <InnerException i:nil="true" />
 <Message>More than one root element in the generated schema - [status,definitions]</Message>
 <StackTrace>at SolarWinds.InformationService.Serialization.XmlResponseSerializerAutoStrict.MakeSchema(IEnumerable`1 columnsMetadata) at SolarWinds.InformationService.Serialization.XmlResponseSerializerAutoStrict.EmitHierarchicalSchema(XmlWriter writer, IEnumerable`1 columnsMetadata) at SolarWinds.InformationService.Serialization.XmlResponseSerializerAutoStrict.Serialize(XmlWriter writer) at SolarWinds.InformationService.Serialization.XmlSerializer.Serializable.WriteXml(XmlWriter writer) at SolarWinds.InformationService.Serialization.BufferedSerializable.InitializeXml() at SolarWinds.InformationService.Core.InformationService.Query(String queryString, SafePropertyBag parameters, IMessageFormatterStrategy messageFormatterStrategy) at SyncInvokeQuery(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)</StackTrace>
 <Type>System.InvalidOperationException</Type>
 </ExceptionDetail>
 </s:Detail>
 </s:Fault>

Thanks

Find more posts tagged with

Accepted answers

All comments

tony.johnson

interesting, this is not something I have used frequently. Do you have any other examples of working queries with similar joins returning XML AUTO?

dbai

Hello Tony, I have tried many different queries and they all seem to fail with XML AUTO if there are selects from multiple tables. I uncovered this behaviour when moving some code to the v3 service, running against the v1 or v2 services seems fine and returns properly defined XML. I have only seen this error mentioned on this forum once here in 2014 https://thwack.solarwinds.com/t5/Orion-SDK-Discussions/Behavioral-difference-between-SWISv2-and-SWISv3-SOAP-issue-with/m-p/246919

Here is the same query running against a v2 service that returns properly defined XML:

<?xml version="1.0" encoding="UTF-8"?>
<QueryXmlResponse xmlns="http://schemas.solarwinds.com/2007/08/informationservice">
   <QueryXmlResult>
      <queryResult>
         <template>
            <entity name="status" type="Orion.AlertStatus" dynamic="False">
               <property name="AlertID" type="Guid" />
               <entitySet name="definitions">
                  <entity name="AlertDefinitions" type="Orion.AlertDefinitions" dynamic="False">
                     <property name="AlertDef" type="String" />
                     <property name="AlertDefID" type="Guid" />
                  </entity>
               </entitySet>
            </entity>
         </template>
         <data>
            <status>
               <AlertID>fc34674e-37b6-4e21-ac92-f74966054337</AlertID>
               <definitions>
                  <AlertDefinitions>
                     <AlertDef>Path to Google</AlertDef>
                     <AlertDefID>fc34674e-37b6-4e21-ac92-f74966054337</AlertDefID>
                  </AlertDefinitions>
               </definitions>
            </status>
            <status>
               <AlertID>70bb013b-3c9e-45b4-899c-81fca2ded205</AlertID>
               <definitions>
                  <AlertDefinitions>
                     <AlertDef>Group test</AlertDef>
                     <AlertDefID>70bb013b-3c9e-45b4-899c-81fca2ded205</AlertDefID>
                  </AlertDefinitions>
               </definitions>
            </status>
         </data>
      </queryResult>
   </QueryXmlResult>
</QueryXmlResponse>

tdanner

RETURN XML AUTO is intended for use with navigation properties. As part of query processing, navigation properties are transformed into joins but also annotated with structural information that later gets used to create the XML AUTO format. There is no SWQL syntax for this structural information - it only exists in the internal data structures used by the query processor.

At least, that's how it works in the v3 service. It has been so long since I looked at the code for the v2 service that I have no idea how it worked there.

So basically to get the output you want, use navigation properties instead of joins.

Another option (easier to parse, in my opinion) would be to use the JSON format, but if you have existing code that works with the XML AUTO format I can see why you would stick with that.

dbai

Thanks for the detailed reply, the issue does seem like an oversight in the v3 endpoint. I have quite a bit of code using the XML format and JOINs for queries, for now have switched to using RAW XML output for affected queries and changed the parsing around as needed. The REST/JSON endpoint does seem better and will probably move over to it in the future.