英文:
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,'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).
答案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
);
英文:
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
);
答案2
得分: 0
正如@Charlieface已经提到的,这看起来像是Babelfish中的一个bug。
似乎它在子查询中添加了一个人工的<_x0078_ml>
根元素。
请尝试下面的一个解决方法。
它的基本思路是通过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 <_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>
答案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('EmpData'),TYPE
)[node()],
(
SELECT prid,
price
FROM #prd
FOR XML RAW('PrdData'),TYPE
)[*]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论