xml - 如何使用 XQuery 以 CSV 格式提取多个 xml 元素？

Question

我正在尝试使用字符串连接函数从 XML 文件中提取多个元素，该函数适用于单个元素。但是，当我尝试将另一个添加到我的代码中时，我看到的数据不正确。我怀疑我在某处遗漏了一个简单的东西，但似乎无法找到它..

示例 XML 数据:-

<books>
  <book id="6636551">
    <master_information>
      <book_xref>
        <xref type="Fiction" type_id="1">72771KAM3</xref>
        <xref type="Non_Fiction" type_id="2">US72771KAM36</xref>
      </book_xref>
    </master_information>
    <book_details>
      <price>24.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications with XML.</description>
    </book_details>
    <global_information>
      <ratings>
        <rating agency="ABC Agency" type="Author Rating">A++</rating>
        <rating agency="DEF Agency" type="Author Rating">A+</rating>
        <rating agency="DEF Agency" type="Book Rating">A</rating>
      </ratings>
    </global_information>
    <country_info>
      <country_code>US</country_code>
    </country_info>
  </book>
  <book id="119818569">
    <master_information>
      <book_xref>
        <xref type="Fiction" type_id="1">070185UL5</xref>
        <xref type="Non_Fiction" type_id="2">US070185UL50</xref>
      </book_xref>
    </master_information>
    <book_details>
      <price>19.25</price>
      <publish_date>2002-11-01</publish_date>
      <description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world.</description>
    </book_details>
    <global_information>
      <ratings>
        <rating agency="ABC Agency" type="Author Rating">A+</rating>
        <rating agency="ABC Agency" type="Book Rating">A</rating>
        <rating agency="DEF Agency" type="Author Rating">A</rating>
        <rating agency="DEF Agency" type="Book Rating">B+</rating>
      </ratings>
    </global_information>
    <country_info>
      <country_code>CA</country_code>
    </country_info>
  </book>
  </book>
</books>

用于拉取单个元素的 XQuery:-

for $x in string-join(('book_id,book_price', //book/book_details/price/string-join((ancestor::book/@id, .), ',')), '
')
return $x

哪个工作正常，并吐出示例输出如下:

book_id,book_price
6636551,24.95
119818569,19.25

问题是如何拉 多个 来自单个 XML 文件的元素或元素和属性的组合，可能仍在使用字符串连接？

我尝试使用以下方法(大部分情况下都可以)，但我注意到对于更大的数据集，值似乎是 填写错误的列随机。例如。如果 ./publish_date 在下面的代码中我注意到的数据中为空白 ./description数据将填充到 ./publish_date柱子。

for $x in string-join(('book_id,book_price,book_pub_date,book_desc', //book/book_details/string-join((ancestor::book/@id, ./price, ./publish_date, ./description), ',')), '
')
return $x

仅供引用，如您所知，我仍在学习 XQuery。感谢您的见解/意见/帮助!

Answer 1

XQuery 中的序列被展平:表达式 (1, (2, 3), ((4)), (), 5)和 (1, 2, 3, 4, 5)是等价的。这意味着序列的长度(ancestor::book/@id, ./price, ./publish_date, ./description)如果某些 XPath 子查询没有返回结果，则会有所不同。由于函数fn:string-join($strings, $sep)只需将每对相邻项目之间的分隔符放在 $strings 中(展平)，结果字符串中可以有不同数量的逗号。

为了保持 CSV 表的对齐方式，您可以在缺少值时插入空字符串。一个简单的方法是利用展平来发挥它的优势:($possibly-empty, '')[1]

如果 $possibly-empty包含一个项目(例如 'foo' )然后计算结果为 ('foo', '')[1] -> 'foo' .

如果是空序列()相反，表达式的计算结果为 ((), '')[1] -> ('')[1] (展平)-> '' .

工作示例(您的封闭 FLWOR 表达式( for/ return )是完全多余的，因为您只迭代单个字符串元素，所以我省略了它):

string-join(
  (
    'book_id,book_price,book_pub_date,book_desc',
    //book/book_details/string-join(
      (
        (ancestor::book/@id, '')[1],
        (./price, '')[1],
        (./publish_date, '')[1],
        (./description, '')[1]
      ),
      ','
    )
  ),
  '
'
)

您还可以将该功能抽象为它自己的功能:

declare function local:non-empty($possibly-empty) {
  ($possibly-empty, '')[1]
};

string-join(
  (
    'book_id,book_price,book_pub_date,book_desc',
    //book/book_details/string-join(
      (
        local:non-empty(ancestor::book/@id),
        local:non-empty(./price),
        local:non-empty(./publish_date),
        local:non-empty(./description)
      ),
      ','
    )
  ),
  '
'
)