1

I'm having an issue with a SQL query that is designed to return the resulting data as XML. Here's a code dump to show you all whats going on:

SQL Query (Note: Table names and column names redacted)

with resultdata as
(
    SELECT 
    (
        select * from Table1 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    ) as tabledata 
    UNION ALL
    SELECT
    (   
        select * from Table2 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type 
    )
    UNION ALL
    SELECT
    (
        select * from Table3 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table4 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table5 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table6 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table7 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
    UNION ALL
    SELECT 
    (
        select * from Table8 (nolock)
        where column1 = 99999 and column2 = -1 for xml auto, type
    )
)

select * from resultdata result for xml auto, elements

This returns an XML result that looks like the following (most of the XML redacted, the comments are where the actual data is):

<result>
  <tabledata>
    <!--Table1 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table2 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table3 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table4 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table5 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table6 results-->
  </tabledata>
</result>
<result>
  <tabledata>
   <!--Table7 results-->
  </tabledata>
</result>
<result>
  <tabledata>
    <!--Table8 results-->
  </tabledata>
</result>

Obviously this is malformed XML, but I can't seem to rework it so that it gives me results in a proper format, something like this:

<result>
  <tabledata>
    <!--Table1 results-->
  </tabledata>
  <tabledata>
    <!--Table2 results-->
  </tabledata>
  <tabledata>
    <!--Table3 results-->
  </tabledata>
  <tabledata>
    <!--Table4 results-->
  </tabledata>
  <tabledata>
    <!--Table5 results-->
  </tabledata>
  <tabledata>
    <!--Table6 results-->
  </tabledata>
  <tabledata>
   <!--Table7 results-->
  </tabledata>
  <tabledata>
    <!--Table8 results-->
  </tabledata>
</result>

As a side note, this is a rewrite of an existing query we have, which is why the syntax is mostly the way it is. A coworker of mine started to rework it and essentially gave me what I have posted here, however I have been struggling with trying to get it to work using his changes. If I need to scrap it though and start from scratch I have no problem with doing that.

Also, as another note, the XML that I want it to look like has to be that way because it is ultimately consumed by an XSLT that I dont have control over. The old version of this query returned each tabledata element as a different column name then did a string replace in the C# code that calls it. There were performance concerns about doing multiple string replaces on this XML (can be 100k+ lines) so the answer was to change the query to return the data the way we need it.

Improve this question
3
  • Removing the "for xml auto, type" from all sub-queries generated an error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I also tried removing the auto piece and just leaving for xml but that gives a syntax error. Commented Sep 3, 2019 at 20:58
  • What is consuming this XML? Could take the results you are getting a replace all instances of "</result><result>" with nothing? Commented Sep 3, 2019 at 21:32
  • Its an XSLT that consumes it but, ultimately thats what Im trying to avoid is string replacing. The current implementation does that and my boss essentially wants that removed for performance reasons. Commented Sep 3, 2019 at 21:34

4 Answers 4

Reset to default
2

Just another option. This will return your desired results.

Select (select * from Table1 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
      ,(Select * from Table2 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
      ,(Select * from Table3 (nolock) where column1 = 99999 and column2 = -1 for XML auto, type, root('tabledata') )
 For XML Path(''),Root('results')
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@KyleRone Happy to help
1

In SQL Server an XML variable, column or FOR XML query result represents an "XML Fragment", without a single top-level Element.

FOR XML queries if you want an XML Document (with a top-level root element) you add the ROOT argument for FOR XML.

Improve this answer

4 Comments

Can you explain a bit more how I can alter my SQL to use this? I tried replacing the final "for xml auto, elements" on the last line with "for xml auto, root" and all that did was add a root element around the malformed xml in my question. I need to have the root element be called result, like in my question.
Other than lacking a single root element, how is the XML malformed?
Thats what I mean by that, there isnt a single root element. But the answer unfortunately isnt that simple as just add a root element because I need the result node to be the root element, updated my question to explain why.
I'm not sure what you need. Can you edit the question to include sample table DDL, source data and desired XML output?
1

If I understand your question correctly you should union your query for each table together and then apply the XML formatting. Like this...

WITH resultdata AS
(
    SELECT (
               SELECT *
               FROM   (
                          SELECT *
                          FROM   Table1 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table2 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table3 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table4 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table5 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table6 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table7 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                          UNION ALL
                          SELECT *
                          FROM   Table8 (NOLOCK)
                          WHERE  column1 = 99999
                                 AND column2 = -1
                      ) AS x
               FOR XML AUTO, TYPE
           ) AS tabledata
)
SELECT * FROM resultdata AS result FOR XML AUTO, ELEMENTS;
Improve this answer

3 Comments

So when I edited my actual query to look like this, I ended up getting this error: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." In the actual query, some of the sub-queries use a * while others explicitly say which columns they want. Could this be the issue? If thats the case then Im not sure this will work, each table is semi-unique but they most certainly do not all have the same amount of columns.
If they do not have the same number of columns you cannot use a UNION.
Ahh well then. I think Im gonna have to go back to the drawing board. Thank you for the knowledge.
0

How about the following solution that allows to construct final XML with its root from the DB tables with different structures.

SQL

-- DDL and sample data population, start
DECLARE @tbl1 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, city VARCHAR(30));
INSERT INTO @tbl1
VALUES
('Miami')
, ('Orlando');

DECLARE @tbl2 TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, [state] VARCHAR(30));
INSERT INTO @tbl2
VALUES
('Florida')
, ('Texas');
-- DDL and sample data population, end

WITH resultdata (tabledata) AS
(
    SELECT 
    (
        SELECT * FROM @tbl1
        FOR XML PATH('row'), TYPE, ROOT('tbl1')
    ) AS [tbl1]
    UNION ALL
    SELECT
    (   
        SELECT * FROM @tbl2
        FOR XML PATH('row'), TYPE, ROOT('tbl2')
    )
)
SELECT * 
FROM resultdata
FOR XML PATH(''), TYPE, ROOT('result');

Output XML

<result>
  <tabledata>
    <tbl1>
      <row>
        <ID>1</ID>
        <city>Miami</city>
      </row>
      <row>
        <ID>2</ID>
        <city>Orlando</city>
      </row>
    </tbl1>
  </tabledata>
  <tabledata>
    <tbl2>
      <row>
        <ID>1</ID>
        <state>Florida</state>
      </row>
      <row>
        <ID>2</ID>
        <state>Texas</state>
      </row>
    </tbl2>
  </tabledata>
</result>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.