XML 到 XML XSLT 的转换.VBScript 中的 MSXML [英] XML to XML XSLT transformation. MSXML in VBScript

问题描述

我有一个相当嵌套的 XML 文件，我想用 XSL 模板将其转换为更简单的文件，以便更高效地将数据批量加载到 SQL 中.我想用 C++(带有 gcc 的代码块)来做这件事，但是我在使用我遇到的任何库(包括 MSXML)加载文档时遇到了一些麻烦.如果有人有在带有 gcc 的 Codeblocks 中使用 MSXML 的经验，请告诉我！

I have a fairly nested XML file that I'd like to transform with an XSL template to something a little simpler to make bulk loading the data into SQL more efficient. I wanted to do it in C++ (Codeblocks with gcc) but I'm having a bit of trouble just being able to load the document with any of the libraries I've come across, including MSXML. If anyone has any experience using MSXML in Codeblocks with gcc let me know!

我有一个样式表，可以使用 DOMDocument 转换 Excel VBA 中的 XML，但我不想依赖 Excel.我认为下一个最好的东西是 VBScript.

I have a stylesheet that transforms the XML in Excel VBA with a DOMDocument but I don't want to depend on Excel. I figured the next best thing would be a VBScript.

数据是保存在 节点中的一个或两个文本值，它是 100 个 节点的后代.每个 节点的第一个子节点，称为 ，为每个 节点保存一个唯一的名称(即；NAME1-NAME100). 节点的第三个和第四个子节点(如果有第四个子节点)是 节点，每个节点都持有一个 ; 节点.该文件可以有超过 100 万个 节点.这是 XML:

The data are one or two text values that are held in <DATAVALUE> nodes, descendants of 100 <LOCATION> nodes. The first child of each <LOCATION> node, called <LOCATIONNAME>, holds a unique name for each <LOCATION> node (i.e; NAME1-NAME100). The third and fourth children of the <LOCATION> node (if there is a fourth child) are <DATA> nodes, each holding a <DATAVALUE> node. The file can have upwards of 1 million <SAMPLE> nodes. Here is the XML:

<?xml version="1.0" encoding="utf-8"?>
<MYImportFile xmlns="urn:ohLookHEREaNamespacedeclaration">
  <HEADERVERSION>1.10</HEADERVERSION>
  <MESSAGE>Import</MESSAGE>
  <MYBED>QUEEN</MYBED>
  <SOURCE>SPRING </SOURCE>
  <USERID>MMOUSE</USERID>
  <DATETIME>2019-11-25T12:31:00</DATETIME>
  <SAMPLE TYPE="No" APPLE="false">
    <SAMPLEID>0000565</SAMPLEID>
    <SAMPLECATEGORY>CLASS5</SAMPLECATEGORY>
    <LOCATION APPLE="false">
      <LOCATIONNAME>NAME1</LOCATIONNAME>
      <READBY>MMOUSE</READBY>
      <TIME>12:31:00</TIME>
      <DATA>
        <DATAVALUE>aaaa</DATAVALUE>
      </DATA>
      <DATA>
        <DATAVALUE>bbbb</DATAVALUE>
      </DATA>
    </LOCATION>
    '''''''''''''''''there are 100 LOCATION entries''''''''''''''''''''''''
    <LOCATION APPLE="false">
      <LOCATIONNAME>NAME100</LOCATIONNAME>
      <READBY>MMOUSE</READBY>
      <TIME>12:31:00</TIME>
      <DATA>
        <DATAVALUE>zzzz</DATAVALUE>
      </DATA>
    </LOCATION>
  </SAMPLE>
  '''''''''''''''''repeat for however many SAMPLES there are''''''''''''''''''''''
</MYImportFile>

我想指出一些事情，以便更清楚地了解发生了什么.在转换后的 xml 文档中，我需要考虑的一件事是 中只有一个 节点.这是通过将第一个 节点复制到新文档中的第二个 节点来完成的.例如，在转换后的工作表中出现两次的 , "zzzz" 仅在初始 XML 中出现一次.这是我希望转换后的 XML 的样子:

I want to point something out so it's a little more clear what's going on. In the transformed xml document, one of the things I need to account for is when there is only one <DATA> node in a <LOCATION>. This is done by copying the first <DATAVALUE> node into a second <DATAVALUE> node in the new document. For example, the <DATAVALUE>, "zzzz" that appears twice in the transformed sheet only appears in the initial XML once. Here is what I want the transformed XML to look like:

<?xml version="1.0" encoding="UTF-8"?>
<MYImportFile>
    <SAMPLE>
        <SAMPLEID>0000565</SAMPLEID>
        <NAME1_1>aaaa</NAME1_1>
        <NAME1_2>bbbb</NAME1_2>
        <NAME2_1>cccc</NAME2_1>
        <NAME2_2>dddd</NAME2_2>
        '''''''''''''''''there are 100 LOCATION entries transformed to NAME1-NAME100''''''''''''''''''''''''
        <NAME100_1>zzzz</NAME100_1>
        <NAME100_2>zzzz</NAME100_2>
    </SAMPLE>
    '''''''''''''''''repeat for however many SAMPLES there are''''''''''''''''''''''
</MYImportFile>

我的样式表(适用于 VBA 代码):

My StyleSheet (that works with VBA code):

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:b="urn:ohLookHEREaNamespacedeclaration" exclude-result-prefixes="b">

<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/b:MYImportFile">

<MYImportFile>

    <xsl:for-each select="b:SAMPLE">

    <SAMPLE>

        <SAMPLEID>
        <xsl:value-of select="b:SAMPLEID"/>
        </SAMPLEID>

        <NAME1_1>
        <xsl:value-of select="b:LOCATION/b:LOCATIONNAME[text() = 'NAME1']/../b:DATA[1]/b:DATAVALUE"/>
        </NAME1_1>

        <xsl:choose> 
            <xsl:when test="b:LOCATION/b:LOCATIONNAME[text() = 'NAME1']/../b:DATA[2]/b:DATAVALUE">
                <NAME1_2>
                <xsl:value-of select="b:LOCATION/b:LOCATIONNAME[text() = 'NAME1']/../b:DATA[2]/b:DATAVALUE"/>
                </NAME1_2>
            </xsl:when>
            <xsl:otherwise>
                <NAME1_2>
                <xsl:value-of select="b:LOCATION/b:LOCATIONNAME[text() = 'NAME1']/../b:DATA[1]/b:DATAVALUE"/>
                </NAME1_2>
            </xsl:otherwise>
        </xsl:choose>


        '''''''''''''''''''there are 100 NAME entires to recieve the 100 locations

    </SAMPLE>

    </xsl:for-each>

</MYImportFile>

</xsl:template>
</xsl:stylesheet>

我的脚本:

Option Explicit

Const strInputFile = "C:\Path\fileName.xml"
Const strTemplateFile = "C:\Path\convFileName.xsl"
Const strOutputFile = "C:\Path\newFileName.xml"

Dim objXMLDoc : Set objXMLDoc = WScript.CreateObject("Msxml2.DOMDocument")
objXMLDoc.async = False
objXMLDoc.loadXML(strInputFile)

objXMLDoc.SetProperty "SelectionNamespaces", "xmlns='urn:myNamespace'"

Dim objXSLDoc : Set objXSLDoc = WScript.CreateObject("Msxml2.DOMDocument")
objXSLDoc.async = False
objXSLDoc.loadXML(strTemplateFile)


Dim objNewXMLDoc : Set objNewXMLDoc = WScript.CreateObject("Msxml2.DOMDocument")

objXMLDoc.transformNodeToObject objXSLDoc, objNewXMLDoc 
objNewXMLDoc.save strOutputFile

错误:

行:19

字符:1

错误:样式表不包含文档元素.这样式表可能为空，也可能不是格式正确的 XML 文档.

Error: The stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed XML document.

代码:80004005

Code: 80004005

来源:msxml3.dll

Source: msxml3.dll

我猜要么我的脚本不太正确，要么我缺少某个设置，导致对象和库不匹配，因为我的 VBA 宏使用该样式表转换了 xml.谁有想法?使这件事运行的建议?

I'm guessing either my script isn't quite right or there's a setting I'm missing, causing mismatching objects and libraries, because my VBA macro transforms the xml with that stylesheet. Anyone have any ideas? Suggestions to make this thing run?

推荐答案

据我所知 loadXML 接受一个带有 XML 的字符串.如果您有要解析的文件或 URL，请使用 load 方法.

As far as I remember loadXML takes a string with the XML. If you have a file or URL to parse use the load method.

这篇关于XML 到 XML XSLT 的转换.VBScript 中的 MSXML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！