XML 显式按父标记分组不起作用-解网

问：

我需要如下输出。但是当我执行我的代码时，我没有将产品正确地分组到每个父级下（它应该像在“美国”国家代码下我们需要三个产品，在“FR”下我们需要两个产品）。

<?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>

法典：

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

sql-server for-xml-explicit

select P1.code as '@Code',
       (
       select P2.manufacturername,
              P2.productname,
              P2.upc
       from @Products as P2
       where P1.code = P2.code
       for xml path('Product'), type
       )
from @Products as P1
group by P1.code
for xml path('Country'), root('ComProducts');

结果：

<ComProducts>
  <Country Code="en-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-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>

上一个：SQL Server FOR XML 在生成同名节点时出现显式错误

下一个：具有内部查询的 Sql Server FOR XML EXPLICIT

XML 显式按父标记分组不起作用

XML explicit group by parent tag is not working

评论