Unexpected XML element "_x0078_ml" in babelfish

Question

create table #emp
(
id int,
name varchar(10)
);
insert into #emp values(1,'A'),(2,'B');

create table #prd
(
prid int,
price int
);
insert into #prd values(1,299),(2,295);

Expected Output: I want to display result as shown below.

<SampleData>
    <EmpData id="1" name="A" />
    <EmpData id="2" name="B" />
    <PrdData prid="1" price="299" />
    <PrdData prid="2" price="295" />
</SampleData>

Query:

SELECT 
(
    SELECT 
    (
        SELECT id,
               name 
        FROM #emp
        FOR XML RAW('EmpData'),TYPE 
    ),
    (
        SELECT prid,
               price
        FROM #prd
        FOR XML RAW('PrdData'),TYPE
    )
    FOR XML PATH('SampleData'),TYPE
);

Output:

<SampleData>
  <_x0078_ml>
    <EmpData id="1" name="A" />
    <EmpData id="2" name="B" />
  </_x0078_ml>
  <_x0078_ml>
    <PrdData prid="1" price="299" />
    <PrdData prid="2" price="295" />
  </_x0078_ml>
</SampleData>

Note: The query works fine in SQL Server but not getting extra XML element _x0078_ml in Babelfish. I need this query to be compatible for both (SQL Server and Babelfish).

Answer 1

Use FOR XML PATH instead, and specify the attribute names explicitly.

SELECT 
(
    SELECT 
    (
        SELECT id AS [@id],
               name AS [@name]
        FROM #emp
        FOR XML PATH('EmpData'),TYPE 
    ),
    (
        SELECT prid AS [@prid],
               price AS [@price]
        FROM #prd
        FOR XML PATH('PrdData'),TYPE
    )
    FOR XML PATH('SampleData'),TYPE
);

Alternatively use FOR XML AUTO, although this is more difficult to control the exact shape of the XML.

SELECT 
(
    SELECT 
    (
        SELECT id,
               name 
        FROM #emp AS EmpData
        FOR XML AUTO,TYPE 
    ),
    (
        SELECT prid,
               price
        FROM #prd AS PrdData
        FOR XML AUTO,TYPE
    )
    FOR XML PATH('SampleData'),TYPE
);

db<>fiddle

Answer 2

As @Charlieface already said, it looks like a bug in Babelfish.

It seems that it is adding an artificial <_x0078_ml> root element in the sub-queries.

Please try a workaround below for that.

Its basic idea to introduce a post-processing step via XQuery to remove not needed sub-queries root element.

SQL

-- DDL and sample data population, start
DECLARE @emp table (id int, name varchar(10));
INSERT @emp values(1,'A'),(2,'B');

DECLARE @prd TABLE (prid int, price int);
INSERT @prd values(1,299),(2,295);
-- DDL and sample data population, end

SELECT (
SELECT 
(
    SELECT id AS [@id],
        name AS [@name]
    FROM @emp
    FOR XML PATH('EmpData'),TYPE, ROOT('root') 
),
(
    SELECT prid AS [@prid],
        price AS [@price]
    FROM @prd
    FOR XML PATH('PrdData'),TYPE, ROOT('root') 
)
FOR XML PATH(''),TYPE, ROOT('SampleData')
).query('<SampleData>
{
    for $x in /SampleData/root/*
    return $x
}
</SampleData>');

Output

<SampleData>
  <EmpData id="1" name="A" />
  <EmpData id="2" name="B" />
  <PrdData prid="1" price="299" />
  <PrdData prid="2" price="295" />
</SampleData>

Answer 3

Have you tried aliasing the xml subqueries with node() or * ?

SELECT 
(
    SELECT id,
           name 
    FROM #emp
    FOR XML RAW('EmpData'),TYPE 
)[node()],
(
    SELECT prid,
           price
    FROM #prd
    FOR XML RAW('PrdData'),TYPE
)[*]