使用XQuery从XML生成CSV：section_listing表格格式异常问题

我尝试用XQuery将给定XML转换为对应两个关系数据库表的CSV文件，其中course.csv生成结果符合预期，但section.csv的列数据对应混乱，无法正确匹配各组件。以下是当前使用的XQuery代码及XML源数据：

当前XQuery代码 #

file:write("course.csv",
    for $x in doc("uwm.xml")//course_listing
    let $y := string-join($x/*[not(name()="section_listing")],"zzcommazz")
    let $y := concat($y,"
")
    let $y := replace($y,'zzcommazz', ',')
    let $y := replace($y,'"', '"')
    return $y
)


file:write("section.csv",
for $x in doc("uwm.xml")//section_listing
let $y := string-join($x/*[not(name()="course_listing")],"zzcommazz")
let $y := concat($y,"
")
let $y := replace($y,'zzcommazz', ',')
let $y := replace($y,'"', '"')
return $y
)

XML源数据 #

<?xml version='1.0' ?>
<root>
<course_listing>
  <note>#</note>
  <course>216-088</course>
  <title>NEW STUDENT ORIENTATION</title>
  <credits>0</credits>
  <level>U</level>
  <restrictions>; ; REQUIRED OF ALL NEW STUDENTS. PREREQ: NONE</restrictions>
   <section_listing>
      <section_note></section_note>
      <section>Se 001</section>
      <days>W</days>
      <hours>
          <start>1:30pm</start>
          <end></end>
      </hours>
      <bldg_and_rm>
          <bldg>BUS</bldg>
          <rm>S230</rm>
      </bldg_and_rm>
      <instructor>Gusavac</instructor>
      <comments>9 WKS BEGINNING WEDNESDAY, 9/6/00 </comments>
   </section_listing>
   <section_listing>
      <section_note></section_note>
      <section>Se 002</section>
      <days>F</days>
      <hours>
          <start>11:30am</start>
          <end></end>
      </hours>
      <bldg_and_rm>
          <bldg>BUS</bldg>
          <rm>S171</rm>
      </bldg_and_rm>
      <instructor>Gusavac</instructor>
      <comments>9 WKS BEGINNING FRIDAY, 9/8/00 </comments>
   </section_listing>
</course_listing>

<course_listing>
  <note>#</note>
  <course>216-293</course>
  <title>BUSINESS ETHICS</title>
  <credits>3</credits>
  <level>U</level>
  <restrictions>; ; PREREQ: NONE</restrictions>
   <section_listing>
      <section_note></section_note>
      <section>Se 001</section>
      <days>R</days>
      <hours>
          <start>2:30pm</start>
          <end>5:10pm</end>
      </hours>
      <bldg_and_rm>
          <bldg>BUS</bldg>
          <rm>S230</rm>
      </bldg_and_rm>
      <instructor>Silberg</instructor>
   </section_listing>
</course_listing>
</root>

问题原因 #

1. 嵌套节点未正确解析：section_listing包含hours、bldg_and_rm这类嵌套节点，原代码直接取$x/*会把这些父节点本身作为元素加入拼接，而不是提取它们的子节点内容，导致CSV中出现空值或错误层级的数据。
2. 列顺序无保障：原代码依赖XML节点的自然顺序生成CSV列，但如果不同section_listing的节点缺失（比如第三个section没有comments），会导致后续列全部错位。
3. 缺少关联外键：section表没有对应course的编号（外键），关系数据库中section必须关联所属course，否则数据失去关联意义。

修正后的XQuery代码 #

-- 生成course.csv，保持原有逻辑并补充列头（可选）
file:write("course.csv",
    let $header := "note,course,title,credits,level,restrictions
"
    let $rows := for $x in doc("uwm.xml")//course_listing
                 let $cols := (
                     $x/note,
                     $x/course,
                     $x/title,
                     $x/credits,
                     $x/level,
                     $x/restrictions
                 )
                 let $escaped := replace(string-join($cols, ","), '"', '"')
                 return concat($escaped, "
")
    return concat($header, $rows)
)

-- 生成section.csv，处理嵌套节点、指定列顺序、添加course外键
file:write("section.csv",
    let $header := "course,section_note,section,days,start_time,end_time,bldg,rm,instructor,comments
"
    let $rows := for $course in doc("uwm.xml")//course_listing
                 for $section in $course/section_listing
                 let $cols := (
                     $course/course,
                     $section/section_note,
                     $section/section,
                     $section/days,
                     $section/hours/start,
                     $section/hours/end,
                     $section/bldg_and_rm/bldg,
                     $section/bldg_and_rm/rm,
                     $section/instructor,
                     $section/comments
                 )
                 -- 处理空值，将空节点转为空字符串
                 let $processed := for $col in $cols return if (empty($col)) then "" else string($col)
                 let $escaped := replace(string-join($processed, ","), '"', '"')
                 return concat($escaped, "
")
    return concat($header, $rows)
)

修正说明 #

• 显式指定每一列的来源，确保列顺序固定，不会因节点缺失错位。
• 解析嵌套节点（hours/start、bldg_and_rm/bldg等），提取实际需要的字段内容。
• 为section添加course字段作为外键，关联对应的课程。
• 处理空节点，将其转为空字符串，避免CSV出现不连贯的分隔符。
• 可选添加CSV列头，让文件结构更清晰。

内容的提问来源于stack exchange，提问作者user19485723