如何将 SQL 表行与 XML 命名空间元素匹配? [英] How do I match SQL table rows with XML namespace elements?

问题描述

我已经成功保存了这个 XML 文件使用以下 PHP 发送到我的服务器:

file_put_contents("test.xml", fopen("http://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/XmlView.aspx?data=yieldyear&year=2015", 'r'));

现在我正在尝试将其放入我的数据库中.只是试图通过在 phpMyAdmin GUI 中执行 SQL 来让它工作.我成功设置了下表:

创建表`test`(`NEW_DATE` varchar(40) 非空，`BC_1MONTH` int(11) NULL，`BC_3MONTH` int(11) NULL，主键(`NEW_DATE`)) 引擎=InnoDB 默认字符集=utf8

以下代码运行时没有错误，但只会在我的数据库中添加一个空行.

加载 XML 本地文件 'test.xml'INTO TABLE测试由'<content>'标识的行；

我看到这里 MySQL 服务器的样子对于与目标表的列名匹配的字段名.还提到没有要求 XML 文件中的每个字段都与相应表中的列匹配.跳过没有相应列的字段.我的列名是否因为 XML 前缀和命名空间而与 XML 不匹配，还是我在树错了树?

这个问题展示了如何引用相同的 XML使用 PHP 的元素.显然，为了构建 Xpath，需要使用 registerXPathNamespace() 定义前缀:

我需要在 SQL 中构建这样的 Xpath 吗?

也许我可以在使用 LIBXML_NSCLEAN;显然这删除了冗余的命名空间声明.什么是多余的?

另一个移除命名空间的选项似乎是 XSL 样式表.根据这个问题.

解决此问题的最佳方法是什么?

解决方案

本质上，您的 XML 太复杂(嵌套节点、属性、命名空间)，无法使用 LOAD XML 轻松导入 MySQL.如您上面的链接所示，该语句仅支持三种不同的格式:

<行><column1>value1</column1><column2>value2</column2></row><行><field name='column1'>value1</field><field name='column2'>value2</field></row>

因此，您需要将原始 XML 转换为与表格字段对齐的上述格式.使用 XSLT 可以提供极大的帮助.作为信息，XSLT 是一种特殊的编程语言，可将 XML 内容重新构建为各种形式以满足最终使用需求.与其他通用语言(包括 Java、C#、Python 和 VB)一样，PHP 配备了 XSLT 1.0 处理器.您可能需要启用扩展程序.

XSLT 脚本 (另存为 .xsl 或 .xslt 文件)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"xmlns:doc="http://www.w3.org/2005/Atom"xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"exclude-result-prefixes="doc m d"><xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/><xsl:strip-space elements="*"/><!-- 身份转换--><xsl:template match="@*|node()"><xsl:copy><xsl:apply-templates select="node()"/></xsl:copy></xsl:模板><!-- 解析需要的内容--><xsl:template match="doc:entry"><行><NEW_DATE><xsl:value-of select="doc:content/m:properties/d:NEW_DATE"/></NEW_DATE><BC_1MONTH><xsl:value-of select="doc:content/m:properties/d:BC_1MONTH"/></BC_1MONTH><BC_3MONTH><xsl:value-of select="doc:content/m:properties/d:BC_3MONTH"/></BC_3MONTH></row></xsl:模板><!-- 删除不需要的节点--><xsl:template match="doc:title|doc:id|doc:update|doc:link|doc:updated"/></xsl:stylesheet>

PHP 脚本 (加载和处理 XML 和 XSL 内容)

$doc = new DOMDocument();//直接从网页解析$doc->load('http://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/XmlView.aspx?data=yieldyear&year=2015');$xsl = 新的 DOMDocument;$xsl->load('XSLTScript.xsl');//配置变压器$proc = 新的 XSLTProcessor;$proc->importStyleSheet($xsl);//转换 XML 源$newXml = $proc->transformToXML($doc);//保存输出到文件$xmlfile = '输出.xml';file_put_contents($xmlfile, $newXml);

XML 输出(现在 XML 内容可以在 LOAD XML 中使用以导入 MySQL)

<NEW_DATE>2015-01-02T00:00:00</NEW_DATE><BC_1MONTH>0.02</BC_1MONTH><BC_3MONTH>0.02</BC_3MONTH></row><行><NEW_DATE>2015-01-05T00:00:00</NEW_DATE><BC_1MONTH>0.02</BC_1MONTH><BC_3MONTH>0.03</BC_3MONTH></row><行><NEW_DATE>2015-01-06T00:00:00</NEW_DATE><BC_1MONTH>0.02</BC_1MONTH><BC_3MONTH>0.03</BC_3MONTH></row>...

I've successfully saved down this XML file to my server using the following PHP:

file_put_contents("test.xml", fopen("http://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/XmlView.aspx?data=yieldyear&year=2015", 'r'));

Now i'm trying to get it into my DB. Just trying to get it to work by executing SQL in the phpMyAdmin GUI. I successfully setup the following table:

CREATE TABLE `test` (
  `NEW_DATE` varchar(40) NOT NULL,
  `BC_1MONTH` int(11) NULL,
  `BC_3MONTH` int(11) NULL,
  PRIMARY KEY (`NEW_DATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The following code runs without error, but just results in the addition of one empty row to my DB.

LOAD XML LOCAL INFILE 'test.xml'
INTO TABLE test
ROWS IDENTIFIED BY '<content>';

I see here that MySQL server looks for field names matching the column names of the target table. It is also mentioned that there is no requirement for every field in the XML file be matched with a column in the corresponding table. Fields which have no corresponding columns are skipped. Are my column names not matching the XML because of the XML prefixes and namespaces or am I barking up the wrong tree?

This question shows how to reference the same XML elements using PHP. Apparently prefix definition is required using registerXPathNamespace() in order to build an Xpath:

Do I need to build such an Xpath in SQL?

Perhaps I could remove the namespace/prefix data in PHP when I save the XML down using something like LIBXML_NSCLEAN; apparently this removes redundant namespace declarations. What qualifies as redundant?

Another option for removing namespaces seems to be XSL stylesheets. Per this question.

What is the best approach to solving this issue?

解决方案

Essentially, your XML is too complex (nested nodes, attributes, namespaces) to readily import into MySQL using LOAD XML. As your above link shows the statement supports only three different formats:

<row column1="value1" column2="value2" .../>

<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>

<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>

Therefore, you need to transform your raw XML into such format above aligned of course to your table's fields. Using XSLT can help tremendously. As information, XSLT is a special programming language that re-structures XML content to various forms for end-use needs. Like other general purpose languages including Java, C#, Python, and VB, PHP comes equipped with an XSLT 1.0 processor. You may need to enable the extension.

XSLT Script (save as .xsl or .xslt file)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                              xmlns:doc="http://www.w3.org/2005/Atom"
                              xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
                              xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
                              exclude-result-prefixes="doc m d">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<!-- IDENTITY TRANSFORM -->
<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="node()"/>
    </xsl:copy>
</xsl:template>

<!-- PARSING NEEDED CONTENT -->
<xsl:template match="doc:entry">
    <row>
        <NEW_DATE><xsl:value-of select="doc:content/m:properties/d:NEW_DATE"/></NEW_DATE>
        <BC_1MONTH><xsl:value-of select="doc:content/m:properties/d:BC_1MONTH"/></BC_1MONTH>
        <BC_3MONTH><xsl:value-of select="doc:content/m:properties/d:BC_3MONTH"/></BC_3MONTH>
    </row>
</xsl:template>

<!-- REMOVE UNNEEDED NODES -->
<xsl:template match="doc:title|doc:id|doc:update|doc:link|doc:updated"/>

</xsl:stylesheet>

PHP Script (loading and processing XML and XSL content)

$doc = new DOMDocument();

// PARSING DIRECTLY FROM WEB PAGE
$doc->load('http://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/XmlView.aspx?data=yieldyear&year=2015');

$xsl = new DOMDocument;
$xsl->load('XSLTScript.xsl');

// Configure the transformer
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl); 

// Transform XML source
$newXml = $proc->transformToXML($doc);

// Save output to file
$xmlfile = 'Output.xml';
file_put_contents($xmlfile, $newXml);

XML Output (now XML content can be used in LOAD XML to import into MySQL)

<?xml version="1.0" encoding="UTF-8"?>
<pre>
  <row>
    <NEW_DATE>2015-01-02T00:00:00</NEW_DATE>
    <BC_1MONTH>0.02</BC_1MONTH>
    <BC_3MONTH>0.02</BC_3MONTH>
  </row>
  <row>
    <NEW_DATE>2015-01-05T00:00:00</NEW_DATE>
    <BC_1MONTH>0.02</BC_1MONTH>
    <BC_3MONTH>0.03</BC_3MONTH>
  </row>
  <row>
    <NEW_DATE>2015-01-06T00:00:00</NEW_DATE>
    <BC_1MONTH>0.02</BC_1MONTH>
    <BC_3MONTH>0.03</BC_3MONTH>
  </row>
  ...
</pre>

这篇关于如何将 SQL 表行与 XML 命名空间元素匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！