mysql - 将 SQL 表导出到 XML 文件并合并标签

Question

我创建了一个 SQL 表，其中包含大量需要导出到 XML 的列，但 XML 需要采用特定格式，而且我似乎无法让它看起来像它应该的那样:

<a c1="aa" c2="ab">
  <b c3="ac" c15="an">
    <c c4="ad" c5="ae" c6="af" c7="ag" c8="ah" c9="ad">
        <d c10="ai" c11="aj" c12="ak" c13="al">am</d>
        <d c10="bi" c11="bj" c12="bk" c13="bl">bm</d>
        <d c10="ci" c11="cj" c12="ck" c13="cl">cm</d>
    </c>
    --Next item for c
    <c>
        <d></d>
        <d></d>
        <d></d>     
    </c>
</d>
    ---and so on and on`

当前查询:

create table #t
(
  c1 VARCHAR(max), c2 VARCHAR(max),  c3 VARCHAR(max),  c4 VARCHAR(max),  c5         VARCHAR(max),  c6 VARCHAR(max),
 c7 VARCHAR(max),  c8 VARCHAR(max),  c9 VARCHAR(max),  c10 VARCHAR(max),     c11 VARCHAR(max),  c12 VARCHAR(max),
  c13 VARCHAR(max),   c14 VARCHAR(max),  c15 VARCHAR(max)
) 
GO

INSERT INTO #t
VALUES ('aa','ab','ac','ad','ae','af','ag','ah','ad','ai','aj','ak','al','am','an'),
      ('ba','bb','bc','bd','be','bf','bg','bh','bd','bi','bj','bk','bl','bm','bn'),
       ('ca','cb','cc','cd','ce','cf','cg','ch','cd','ci','cj','ck','cl','cm','cn')


SELECT c1 as 'a/@c1', c2 as 'a/@c2',
       c3 as 'a/b/@c3', c15 as 'a/b/@c15',
       c4 as 'a/b/c/@c4', c5 as 'a/b/c/@c5',
       c6 as 'a/b/c/@c6', c7 as 'a/b/c/@c7',
       c8 as 'a/b/c/@c8', c9 as 'a/b/c/@c9',
       c10 as 'a/b/c/d/@c10', c11 as 'a/b/c/d/@c11',
       c12 as 'a/b/c/d/@c12', c13 as 'a/b/c/d/@c13',
       c14 as 'a/b/c/d'
FROM #t
  FOR XML PATH('')--, ROOT('')

这会产生

<a c1="aa" c2="ab"> 
  <b c3="ac" c15="an">
    <c c4="ad" c5="ae" c6="af" c7="ag" c8="ah" c9="ad">
      <d c10="ai" c11="aj" c12="ak" c13="al">am</d>
    </c>
  </b>
</a>
<a c1="ba" c2="bb">
  <b c3="bc" c15="bn">
    <c c4="bd" c5="be" c6="bf" c7="bg" c8="bh" c9="bd">
      <d c10="bi" c11="bj" c12="bk" c13="bl">bm</d>
     </c>
  </b>
</a>
<a c1="ca" c2="cb">
  <b c3="cc" c15="cn">
    <c c4="cd" c5="ce" c6="cf" c7="cg" c8="ch" c9="cd">
      <d c10="ci" c11="cj" c12="ck" c13="cl">cm</d>
    </c>
  </b>
</a>

我尝试使用子选择运行选择(抱歉，代码仍在工作，我不记得到底如何做到这一点)但是 b 标记之后的所有子后续项目都失去了格式(写入 > 而不是 >)

再次感谢您的宝贵时间，非常感谢

Answer 1

美好的一天，如果有人设法找到类似的问题，下面是我如何修复它，遗憾的是我没有时间将变量重命名为上面的示例:

  (select 
  a1.CONTINENTID as '@id', a1.CONTINENTNAME as '@name',
   (select a2.COUNTRYID as '@id', a2.COUNTRYNAME as '@name',
      (select CENTREID as '@id', CENTRENAME as'@name', CENTRECODE as '@code',
              CENTRELATITUDE as '@lat', CENTRELONGITUDE as '@lon', CENTRESTATUS as '@status',
              (select HEADING as '@text', HEADINGSTATUS as '@status',
               (select
                  HEADINGITEM as 'Collapsableheadingitem/@text', 
                  HEADINGITEMSTATUS as 'Collapsableheadingitem/@status',
                  HEADINGITEMDESCRIPTION as 'Collapsableheadingitem'
                  From OIAUI as a5
                  where a5.HEADING = a4.HEADING and
                        a5.CENTREID = a3.CENTREID and
                        a5.COUNTRYID = a2.COUNTRYID and
                        a5.CONTINENTID = a1.CONTINENTID
                  GROUP BY a5.STATUSID, HEADINGITEM, HEADINGITEMSTATUS, HEADINGITEMDESCRIPTION
                  ORDER BY a5.STATUSID DESC
                  for xml path(''), type         
                )
                FROM OIAUI as a4
                WHERE a4.CENTREID = a3.CENTREID and
                      a4.COUNTRYID = a2.COUNTRYID and
                      a4.CONTINENTID = a1.CONTINENTID
                GROUP by   a4.HEADING, a4.HEADINGSTATUS
                ORDER BY   a4.HEADING
                FOR XML PATH('Collapsableheading'), type
               )
       from OIAUI as a3
       where a3.CONTINENTID = a1.CONTINENTID and
             a3.COUNTRYID = a2.COUNTRYID
       group by a3.CENTREID, a3.CENTRENAME, a3.CENTRECODE, a3.CENTRELATITUDE, a3.CENTRELONGITUDE, a3.CENTRESTATUS
       ORDER BY a3.CENTREID
       for xml path('CashCentre'), type      
      )
    from OIAUI as a2
    where a1.CONTINENTID = a2.CONTINENTID
    group by a2.COUNTRYID, a2.COUNTRYNAME
    ORDER BY a2.COUNTRYID
    for xml path('Country'), type
  )  
from OIAUI as a1
group by CONTINENTID, CONTINENTNAME
ORDER BY a1.CONTINENTID
for xml path('Continent')
)