Unexpected XML element “_x0078_ml” in babelfish

huangapple go评论69阅读模式
英文:

Unexpected XML element "_x0078_ml" in babelfish

问题

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>
<EmpData id="1" name="A" />
<EmpData id="2" name="B" />
<PrdData prid="1" price="299" />
<PrdData prid="2" price="295" />
</SampleData>

英文:
create table #emp
(
id int,
name varchar(10)
);
insert into #emp values(1,&#39;A&#39;),(2,&#39;B&#39;);

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.

&lt;SampleData&gt;
	&lt;EmpData id=&quot;1&quot; name=&quot;A&quot; /&gt;
	&lt;EmpData id=&quot;2&quot; name=&quot;B&quot; /&gt;
	&lt;PrdData prid=&quot;1&quot; price=&quot;299&quot; /&gt;
	&lt;PrdData prid=&quot;2&quot; price=&quot;295&quot; /&gt;
&lt;/SampleData&gt;

Query:

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

Output:

&lt;SampleData&gt;
  &lt;_x0078_ml&gt;
	&lt;EmpData id=&quot;1&quot; name=&quot;A&quot; /&gt;
	&lt;EmpData id=&quot;2&quot; name=&quot;B&quot; /&gt;
  &lt;/_x0078_ml&gt;
  &lt;_x0078_ml&gt;
	&lt;PrdData prid=&quot;1&quot; price=&quot;299&quot; /&gt;
	&lt;PrdData prid=&quot;2&quot; price=&quot;295&quot; /&gt;
  &lt;/_x0078_ml&gt;
&lt;/SampleData&gt;

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

答案1

得分: 1

使用 FOR XML PATH,并明确指定属性名称。

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

或者使用 FOR XML AUTO,尽管这更难以控制 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

英文:

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(&#39;EmpData&#39;),TYPE 
    ),
    (
        SELECT prid AS [@prid],
               price AS [@price]
        FROM #prd
        FOR XML PATH(&#39;PrdData&#39;),TYPE
    )
    FOR XML PATH(&#39;SampleData&#39;),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(&#39;SampleData&#39;),TYPE
);

db<>fiddle

答案2

得分: 0

正如@Charlieface已经提到的,这看起来像是Babelfish中的一个bug。

似乎它在子查询中添加了一个人工的&lt;_x0078_ml&gt;根元素。

请尝试下面的一个解决方法。

它的基本思路是通过XQuery引入一个后处理步骤,以删除不需要的子查询根元素。

SQL

-- DDL和样本数据填充,开始
DECLARE @emp表(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和样本数据填充,结束

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('
{
for $x in /SampleData/root/*
return $x
}
');

输出






英文:

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

It seems that it is adding an artificial &lt;_x0078_ml&gt; 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,&#39;A&#39;),(2,&#39;B&#39;);

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(&#39;EmpData&#39;),TYPE, ROOT(&#39;root&#39;) 
),
(
    SELECT prid AS [@prid],
		price AS [@price]
    FROM @prd
    FOR XML PATH(&#39;PrdData&#39;),TYPE, ROOT(&#39;root&#39;) 
)
FOR XML PATH(&#39;&#39;),TYPE, ROOT(&#39;SampleData&#39;)
).query(&#39;&lt;SampleData&gt;
{
	for $x in /SampleData/root/*
	return $x
}
&lt;/SampleData&gt;&#39;);

Output

&lt;SampleData&gt;
	&lt;EmpData id=&quot;1&quot; name=&quot;A&quot; /&gt;
	&lt;EmpData id=&quot;2&quot; name=&quot;B&quot; /&gt;
	&lt;PrdData prid=&quot;1&quot; price=&quot;299&quot; /&gt;
	&lt;PrdData prid=&quot;2&quot; price=&quot;295&quot; /&gt;
&lt;/SampleData&gt;

答案3

得分: 0

你尝试过将 XML 子查询使用 node() 或 * 进行别名处理吗?

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

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

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

huangapple
  • 本文由 发表于 2023年6月26日 18:49:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/76555979.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定