XML显式输出：按父标记分组不起作用 [英] XML explicit output: group by parent tag is not working
本文介绍了XML显式输出：按父标记分组不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要如下输出。但是当我执行我的代码时，我没有正确地将产品分组到每个父母下面（应该像在美国国家代码下我们需要有三个产品而在FR下我们需要有两个产品）。我也在这里放置了代码，请尽快帮助我。
<？ xml version = 1.0 encoding = utf-8 ？ >
< ComProducts >
< 国家/地区 代码 = 美国 >
< 产品 >
< manufacturername > abc < / manufacturername >
< productname > xyz road < / productname >
< upc > RJ < / upc >
< / Product >
< 产品 >
< manufacturername > temp < / manufacturername >
< productname > ppp road < / productname >
< span class =code-keyword>< upc > RJ < / upc >
< / Product >
< 产品 >
< manufacturername > ccc < / manufacturername >
< productname > oli Com < / productname >
< < span class =code-leadattribute> upc > CL < / upc >
< / Product < span class =code-keyword>>
< / Country >
< 国家/地区 代码 = FR >
< < span class =code-leadattribute>产品 >
< manufacturername > xxx < / manufacturername >
< productname > aaa road < span class =code-keyword>< / productname >
< upc > NY < span class =code-keyword>< / upc >
< / Product >
< 产品 >
< manufacturername > eee < / manufacturername >
< productname > olkiu road < / productname >
< upc > CL < / upc >
< / Product >
< / Country >
< / ComProducts >
代码：
DECLARE @ Products TABLE
（
代码 VARCHAR （ 10 ），
manufacturername VARCHAR （ 50 ），
productname NVARCHAR （ 255 ），
upc VARCHAR （ 100 ）
）
INSERT 我NTO @ Products
选择 ' en-us'，' abc '，' xyz road'，' RJ' union 所有
选择 ' en-us'，' temp'，' ppp road'，' RJ' < span class =code-keyword> union all
选择 ' fr-fr'，' < span class =code-string> xxx'，' aaa road'，< span class =code-string>' NY' union all
选择 ' en-us'，' ccc'，' oli Com'，' CL' union all
选择 ' fr-fr'，' eee'，' olkiu road'，' CL'
SELECT 1 AS 标签，
NULL AS 父级，
NULL AS ' ComProducts！1！'，
NULL AS ' 国家/地区！2！locale'，
NULL AS ' Products！3！'，
NULL AS ' Products！3！manufacturerName！Element'，
NULL AS ' Products！3！productName！cdata'，
NULL AS ' 产品！3！upc！元素'
UNION ALL
< span class =code-keyword> SELECT 2 AS 标签，
1 AS 父级，
NULL ，
代码，
NULL ，
manufacturername，
productname，
upc
FROM @ Products
UNION 所有
SELECT 3 AS 标记，
2 AS 父级，
< span class =code-keyword> NULL ，
NULL ，
NULL ，
manufacturername，
productname，
upc
FROM @ Products
FOR xml explicit
解决方案
DECLARE @Products TABLE
（
code VARCHAR（10），
manufacturername VARCHAR（50），
productname NVARCHAR（255），
upc VARCHAR（100）
）
INSERT INTO @Products
选择'en-us'，'abc' ，'xyz road'，'RJ'联合所有
选择'en-us'，'temp'，'ppp road'，'RJ'联合所有
选择'fr-fr'，'xxx' ，'aaa road'，'NY'联盟全部
选择'en-us'，'ccc'，'oli Com'，'CL'联合所有
选择'fr-fr'，'eee' ，'olkiu road'，'CL'
SELECT p1 .code为'@Code'，
（
SELECT 1为Tag，
0为父，
p2.manufacturername为[Product！1！manufacturername！ELEMENT]，
p2.productname as [Product！1！productname！CDATA]，
p2.upc as [Product！1！upc！ELEMENT]
FROM @Products p2
WHERE p1.code = p2.code
FOR XML EXPLICIT
）
FROM @Products p1
GROUP BY p1.code
FOR XML PATH（'Country'），ROOT（'ComProducts' ）
I required output like below. But When I execute my code, I am not getting the product properly grouped under each parent (It should be like under "US" country code we need to have three products and under "FR" we need to have two products). I have placed code also here and Pls help me ASAP.
<?xml version="1.0" encoding="utf-8"?>
<ComProducts>
<Country Code="US">
<Product>
<manufacturername>abc</manufacturername>
<productname>xyz road</productname>
<upc>RJ</upc>
</Product>
<Product>
<manufacturername>temp</manufacturername>
<productname>ppp road</productname>
<upc>RJ</upc>
</Product>
<Product>
<manufacturername>ccc</manufacturername>
<productname>oli Com</productname>
<upc>CL</upc>
</Product>
</Country>
<Country Code="FR">
<Product>
<manufacturername>xxx</manufacturername>
<productname>aaa road</productname>
<upc>NY</upc>
</Product>
<Product>
<manufacturername>eee</manufacturername>
<productname>olkiu road</productname>
<upc>CL</upc>
</Product>
</Country>
</ComProducts>
Code :
DECLARE @Products TABLE
(
code VARCHAR(10),
manufacturername VARCHAR(50),
productname NVARCHAR(255),
upc VARCHAR(100)
)
INSERT INTO @Products
select 'en-us', 'abc', 'xyz road', 'RJ' union all
select 'en-us', 'temp', 'ppp road', 'RJ' union all
select 'fr-fr', 'xxx', 'aaa road', 'NY' union all
select 'en-us', 'ccc', 'oli Com', 'CL' union all
select 'fr-fr', 'eee', 'olkiu road', 'CL'
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS 'ComProducts!1!',
NULL AS 'Country!2!locale',
NULL AS 'Products!3!',
NULL AS 'Products!3!manufacturerName!Element',
NULL AS 'Products!3!productName!cdata',
NULL AS 'Products!3!upc!Element'
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
NULL,
code,
NULL,
manufacturername,
productname,
upc
FROM @Products
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
manufacturername,
productname,
upc
FROM @Products
FOR xml explicit 解决方案
DECLARE @Products TABLE
(
code VARCHAR(10),
manufacturername VARCHAR(50),
productname NVARCHAR(255),
upc VARCHAR(100)
)
INSERT INTO @Products
select 'en-us', 'abc', 'xyz road', 'RJ' union all
select 'en-us', 'temp', 'ppp road', 'RJ' union all
select 'fr-fr', 'xxx', 'aaa road', 'NY' union all
select 'en-us', 'ccc', 'oli Com', 'CL' union all
select 'fr-fr', 'eee', 'olkiu road', 'CL'
SELECT p1.code as '@Code',
(
SELECT 1 as Tag,
0 as Parent,
p2.manufacturername as [Product!1!manufacturername!ELEMENT],
p2.productname as [Product!1!productname!CDATA],
p2.upc as [Product!1!upc!ELEMENT]
FROM @Products p2
WHERE p1.code = p2.code
FOR XML EXPLICIT
)
FROM @Products p1
GROUP BY p1.code
FOR XML PATH ('Country'), ROOT ('ComProducts')这篇关于XML显式输出：按父标记分组不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
