Dan's questions

我有下表：

MyTable
PlaceID NameID  Code    CodeCount
1       1       1234A   2
1       2       1234A   3
1       3       1234A   1
2...
2...
3...
3...

我想选择1234Awhere PlaceID= 1，次数等于CodeCount. 因此，结果将是这样的六行：

MyResult
Code    
1234A
1234A
1234A
1234A
1234A
1234A

最终，这个选择将有一堆不同的代码用于单个PlaceID. 是的，我知道我在复制数据，最后它不会很多，我将在下面解释我为什么要这样做。

首先，我需要得到计数的总和：

DECLARE @cdcnt INT = (SELECT SUM(CodeCount) FROM MyTable WHERE PlaceID = 1 AND Code = '1234A')

然后我可以将此值用作while循环中的控件来多次选择该值：

DECLARE @i INT = 0;
WHILE @i < @cdcnt
BEGIN
    SELECT DISTINCT Code
    FROM MyTable
    WHERE PlaceID = 1 AND Code = '1234A'
    SET @i = @i + 1
END

问题是我得到 6 个单独的选择，而不是 6 行的一个选择。如何创建MyResult如上所示，而不是 6 个单独的选择？

为什么我这样做：我想使用随机数生成器来选择一个Code，它的出现频率为每个PlaceID（还会Codes添加其他的，我还在想办法只做一个）。我愿意接受有关不同方法的建议，但这是我能弄清楚的唯一方法。如果已经构建了一个功能来根据设定的频率从列表中进行选择，我会全力以赴。

我之前问过一个关于我收到的错误的问题。您并不真的需要它来理解这个问题，但它在这里供参考：

XML/SQL Server 2008 错误：XQuery...无法隐式原子化或将“fn:data()”应用于复杂的内容元素

以前的 xml 有点复杂，可能会从转换中受益，所以我应用了一个 XSLT 模板来获得下面的结构并稍微更改了标签，使其更易于理解。为了可维护性，我还重组了要导入的表。我将转换后的 XML 文件导入到 SQL Server 表，xTable， ， 列xData， 像这样（只有一行，但我想你可以导入多个 1 并将它们全部与大卫布朗的答案合并）：

ID    xData
1     <MyXMLFile><Sample><Location>....

xml 的父节点<Sample>, 最多可以重复 100 万次，但为了这个说明，我只有 2 个。每个样本有 22 个子节点，一个<SampleID>节点和 21<Location>个节点（我只显示了要保留的 2 个节点事情很短）。每个节点有 3 个子节点，一个<LocationName>节点和两个<Foo>节点，分别指定<Foo1>和<Foo2>。

<?xml version="1.0" encoding="UTF-16"?>
<MyXMLFile>
    <!--There CAN BE up to 1 million <Sample> nodes-->
    <Sample>
        <!--There ARE EXACTLY 22 child nodes for each <Sample> parent node, one <SampleID> and 21 <Location>-->
        <SampleID>0000001A</SampleID>
        <!--There ARE EXACTLY 3 child nodes for each <Location> parent node, on <LocationID> and two <Foo>-->
        <Location>
            <LocationName>Jeff</LocationName>
            <Foo1>10</Foo1>
            <Foo2>11</Foo2>
        </Location>
        <Location>
            <LocationName>Jenn</LocationName>
            <Foo1>11</Foo1>
            <Foo2>12</Foo2>
        </Location>
    </Sample>
    <Sample>
        <SampleID>0000002A</SampleID>
        <Location>
            <LocationName>Greg</LocationName>
            <Foo1>13</Foo1>
            <Foo2>14</Foo2>
        </Location>
        <Location>
            <LocationName>Anne</LocationName>
            <Foo1>14</Foo1>
            <Foo2>16</Foo2>
        </Location>
    </Sample>
</MyXMLFile>

我想转换xData列xTable并将其放入此表（ID 列仅用于说明）：

ID      SampleID    LocationName    Foo1   Foo2
1       00000001    Jeff            10     11     
2       00000001    Jenn            11     12     
…       00000001    …               …      …            
22      00000001    …               …      …     
23      00000002    Greg            13     14    
24      00000002    Anne            17     18
…       00000002    …               …      …
44      00000002    …               …      …     

目前，我只是尝试SELECT从xData列中获取xTable，稍后将编辑查询以插入数据。所以我的第一个查询，只是为了表明<SampleID>确实被选中：

查询 1

SELECT  a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID

FROM xTable

CROSS APPLY xData.nodes('MyXMLFile/Sample') as a(b)

输出看起来不错：

ID      SampleID
1       00000001
2       00000002

所以，我添加到查询中：

查询2

SELECT  a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID,
        a.b.query('LocationName').value('.', 'varchar(10)') AS LocationName,
        a.b.query('Foo1').value('.', 'varchar(6)') AS Foo1,
        a.b.query('Foo2').value('.', 'varchar(6)') AS Foo2

FROM xTable
CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID/../Location') as a(b)

对于此输出，没有为 选择任何数据<SampleID>。这对我来说并不奇怪，因为 xpath 选择只针对<Location>父节点并返回其子节点<LocationName>，<Foo1>而<Foo2>不是<SampleID>。

ID      SampleID    LocationName    Foo1   Foo2
1                   Jeff            10     11     
2                   Jenn            11     12     
…                   …               …      …            
22                  …               …      …     
23                  Greg            13     14    
24                  Anne            17     18     
…                   …               …      …
44                  …               …      …   

所以我尝试了这个：

查询 3

SELECT  a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID,
        c.d.query('LocationName').value('.', 'varchar(10)') AS LocationName,
        c.d.query('Foo1').value('.', 'varchar(6)') AS Foo1,
        c.d.query('Foo2').value('.', 'varchar(6)') AS Foo2
FROM xTable
CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID') as a(b)
CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID/../Location') as c(d)

输出稍微好一点，但表中的行重复了。应该只有 44 个，但有 88 个：

ID      SampleID    LocationName    Foo1   Foo2
1       00000001    Jeff            10     11     
2       00000001    Jenn            11     12     
…       00000001    …               …      …            
42      00000001    …               …      …     
43      00000001    …               …      …
44      00000001    …               …      …
45      00000002    Greg            13     14    
46      00000002    Anne            17     18
…           …       …               …      …
88      00000002    …               …      …

然后我想我会尝试不同的方式。

查询 4

DECLARE @x xml;
SELECT @x = xData
FROM xTable
SELECT a.b.value('(SampleID/text())[1]', 'varchar(20)') AS SampleID,
       a.b.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName,
       a.b.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       a.b.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2

FROM @x.nodes('MyXMLFile/Sample') AS xData(a)
CROSS APPLY @x.nodes('MyXMLFile/Sample/SampleID/../Location') AS a(b)

现在，不是空白SampleID字段或重复记录，而是SampleID返回NULL并且数据被重复：

ID      SampleID    LocationName    Foo1   Foo2
1       NULL        Jeff            10     11     
2       NULL        Jenn            11     12     
…       NULL        …               …      …            
42      NULL        …               …      … 
43      NULL        …               …      …
44      NULL        …               …      …
45      NULL        Greg            13     14    
46      NULL        Anne            17     18
…       NULL        …               …      …
88      NULL        …               …      …

所以在最后一次尝试选择正确的数据时，我尝试了这个查询：

查询 5

DECLARE @x xml;
SELECT @x = xData
FROM xTable
SELECT a.b.value('(SampleID/text())[1]', 'varchar(20)') AS SampleID,
       c.d.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName,
       c.d.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       c.d.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2

FROM @x.nodes('MyXMLFile/Sample') AS xData(a)
CROSS APPLY @x.nodes('MyXMLFile/Sample') AS a(b)
CROSS APPLY @x.nodes('MyXMLFile/Sample/SampleID/../Location') AS c(d)

这里的结果更令我惊讶，查询不仅填充了所有字段，而且使输出翻了两番：

ID      SampleID    LocationName    Foo1   Foo2
1       00000001    Jeff            10     11     
2       00000001    Jenn            11     12     
…       00000001    …               …      …            
…       00000001    …               …      …     
…       00000001    …               …      …    
44      00000001    …               …      …
45      00000002    Greg            13     14   
46      00000002    Anne            17     18
47      00000002    …               …      …
48      00000002    …               …      …
…           …       …               …      …
176     00000002    …               …      …

我理解我的问题是将两个不同的 xpath 合并到查询中，以及我对查询中派生表的理解和使用。任何帮助，将不胜感激。如何调整这些查询以获得我需要的表？

提前致谢。

编辑： 根据大卫布朗的回答，这对我有用：

查询 6

INSERT INTO MyTable (SampleID, LocationName, Foo1, Foo2)
SELECT Sample.n.value('(SampleID)[1]', 'varchar(20)') AS SampleName,
       Location.n.value('(LocationName/text())[1]', 'varchar(1)') AS LocationName,
       Location.n.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       Location.n.value('(Foo2/text())[1]', 'varchar(6)') As Foo2
FROM xTable AS x
CROSS APPLY x.xData.nodes('/MYXMLFile/Sample') AS Sample(n)
CROSS APPLY Sample.n.nodes('Location') AS Location(n)

我有三张桌子：

表格1

table1可以有 50K 到 800k 的唯一行。我的意思是每行中所有值的组合都是唯一的，尽管有时某些列会匹配。除了列之外，整行都可能匹配NAME，但这是非常非常不可能的。NAME 列将始终是唯一的。

该NAME列是 type varchar(20)。每条记录的其余列都是 type varchar(6)，其中每两列是一个集合对，表有多达 21 个集合对（即总共 43 列）。这是一个table1显示 4 条记录的一组对的示例（为了方便起见，我在这里使用了单个字符，但不要忘记它们是 type varchar(6)）：

table1

NAME        pair1_1     pair1_2     ...up to pair21_1 - pair21_2
00001A      A           B       
00002A      A           A       
00003A      B           C       
00004A      A           B       
…up to 800k rows

表2

table2设置方式完全相同，只是它包含完全相同类型的完全不同的记录（这里可以有 1 到 200 行）

table2

NAME        pair1_1    pair1_2    ...up to pair21_1 - pair21_2
1234B       A          B
5678B       A          A
9101B       C          C
1213B       A          B        
…up to 200 rows

表3

table3与 in 中的单行相关联，table2并且可以表征为该单行 intable2与可能位于 中的任何可能行之间的比较的每个可能结果table1。最好通过与它相关的NAMEin来调用table2它（让我们只使用第一个并调用它，table1234B因为如果 table2 中有 200 行，则有 200 个不同的关联table3s）。

第 3 个表将包含 4 行。它有一NAME列是 avarchar(20)和 21 组 7 列（每列与 和 的不同对相关联table1）table2。额外的列res1_1是res1_2…res21_7type decimal(30,7)。

这是它的样子：

table1234B

NAME        res1_1  res1_2  res1_3  res1_4  res1_5  res1_6  res1_7  ….res21_7
1234B       12.30   1.000   0.2500  1.000   2.000   2.10    25.00   ….

我想使用来自table1和的列对中的共享数据的组合table2（即它们如何匹配）作为从中选择数据的条件table1234B（我将在下面展示其中的一些）。只会比较重合的对。pair1_1and pair1_2fromtable1将与 topair1_1和pair1_2from进行比较table2，pair2s 将仅与pair2s 进行比较，pair3s 仅与pair3s 进行比较等。因此，不会进行交叉对比较（例如pair1，永远不会与 进行比较pair2）

在下面的示例中，pair1_1每个表中的字段匹配 (A)，pair1_2每个表中的字段匹配 (B)，但每个表的列之间的字段不匹配。

NAME        Pair1_1     Pair1_2
00001A      A           B       (from table1)
1234B       A           B       (from table2)

所以我想 SELECT 说pair1_4fromtable1234B并将其与记录 00001A 和 1234B 之间的比较相关联。

如果表格是这样的，我们可以看到所有 4 个字段都匹配。

NAME        pair1_1     pair1_2
00001A      A           A       (from table1)
1234B       A           A       (from table2)

在这种情况下，也许我想pair1_1选择table1234B

在这里我们可以看到pair1_1intable1匹配两个字段 fromtable2但pair1_2fromtable1不匹配任何内容。

NAME        pair1_1     pair1_2
00001A      A           B       (from table1)
1234B       A           A       (from table2)

所以我想选择say pair1_4fromtable1234B

以上只是列对中的数据可以在表之间共享的 14 种可能方式中的 3 种（它们也有多种方式在表之间不共享数据），但每对只有 7 种可能的列可供选择table1234B。

我想从和table1234B的每组 2 中的列之间的所有可能共享中选择符合标准的所有值。完成后看起来像这样：table1table2

1234BResult

NAME        RESULTPair1     …up to Resultpair21
00001A      12.30       (res1_1 from table3)        
00002B      1.000       (res1_2 from table3)                
00003C      25.00       (res1_7 from table3)        
00004A      1.000       (res1_4 from table3)
…up to 800K rows

这是我开始编辑的查询。

SELECT t1.NAME as NAME, t3.pair1_4 as RESULTPair1
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON (t1.pair1_1 = t2.pair1_1 AND t1.pair1_1 <> t1.pair1_2 AND t1.pair1_2 = t2.pair1_2 AND t2.pair1_1 <> t2.pair1_2)
LEFT OUTER JOIN table1234B t3
ON t2.NAME = t3.NAME

…以及该查询的结果

NAME        RESULTPair1 
00001A      1.000       (res1_4 from table3)
00002B      NULL
00003A      NULL
00004A      1.000       (res1_4 from table3)

这更接近我正在寻找的内容，因为它使NAMEstable2具有不同的共享对来自 的其他值开放table1234B。我打算合并后的剩余逻辑将返回其他适当的结果。

麻烦的是，这只会给我任何情况的结果，即 AB，AB。我需要将其扩展到 21 对和可能table3的 200 对，包括所有不同的可能结果（table3如果适用）。我可以处理所有共享逻辑（即pair1_1 = pairt2_2或pair1_1 <> pair2_2），它将其扩展到其余情况以及可能更多table2我不知所措的记录。我需要获得table1所有 21 对中所有 800k 记录的结果。

如果你和我待了这么久并且理解所有的鸡抓，我的问题是：

1. 我将如何编辑上面的查询以使用 aJOIN而不是 a WHERE？
2. 是否有一种方法可以扩展它以有效地包含在字段之间共享的其他可能场景的逻辑，table1以便table2我可以在新表中查看或存储从table1单行到单行的所有 50-800k 结果table2？
3. 我怎么把它扩展到额外的 20 对？

编辑 在被问及与 table3 的连接后，我意识到该列需要编辑才能正常工作。我仍然无法弄清楚如何使查询正常工作。我也编辑了查询。我仍然在将其扩展到多个列时遇到了一些麻烦。建议使用 INTERSECT + EXCEPT 或 NOT EXISTS。我无法让 INTERSECT 返回任何内容，而带有编辑以包含左连接的查询确实如此。

2018 年 10 月 30 日更新。为了以防万一有人感兴趣，我已经开始在这篇文章的末尾记录我为解决这个问题所做的实际工作，只是因为我发现了一些有趣的事情。

免责声明！我对此很陌生。我对 SQL Server、VBA、XPATH、XSD、XML 的自学接触相当有限，并且接受了一年的逻辑 (Java) 正式培训。大约一年前，我的工作使我进入了一个独特的位置，开始接触 SQL 和 XML，所以我仍然是一个新手。我发布/询问的内容可能非常天真，如果是这样，我道歉并欢迎坦率、严厉、批评、关于功能和形式适当性的建议以及良好的硬拷贝参考（我不是指 Microsoft 文档，因为我很少能理解他们的头或尾）。所以开始吧！

我正在处理的项目中遇到了这个错误，虽然这里和其他地方有一些关于它的帖子，但似乎没有任何帮助。我的猜测是我遇到了 XPATH 表达式问题……最初。

XQuery [XMLTestTable.DATA.value()]：无法隐式原子化或将“fn:data()”应用于复杂内容元素，在推断类型“元素”中找到类型“xs:anyType”({urn:MyFile-schema}:SUBUNITPRICE ,xs:anyType) *'.

正如您可能猜到的那样，这是一个 XML 导入/转换到关系表项目。我在网上发现了很多关于这类事情的信息，但对其中大部分含义的解释却很少（尽管我对其中的大部分内容都有一些有根据的猜测）。

我将从这里开始。模式：

有时人们会先导入一个模式，有时他们不会，只是顺其自然。我理解模式的方式是：1：根据某种标准验证 xml 文档，以确保一切顺利运行/导入/导出 2：可能提高导入/导出/编辑文件的效率。虽然我发现验证文档本身会增加导入时间（我认为这只是额外的步骤），但下游查询可能更有效，尽管我还没有走那么远。无论哪种方式，我认为理所当然并实践它是一个好主意。所以这是我的模式（这是手写的，所以如果你看到不好的地方，请对我大喊大叫！）。另外，我使用了一个名为 SUBUNITPRICE 的节点，请理解这实际上不是货币单位，我 我们更改了一些节点名称以使事情更加机密。只知道这个节点值是一个文本值，可以包含数字和符号。

<?xml version="1.0" encoding="UTF-8"?>
 <xs:schema  xmlns:xs= "http://www.w3.org/2001/XMLSchema" targetNamespace="urn:MyFile-schema" xmlns="urn:MyFile-schema" elementFormDefault="qualified">
         <xs:element name="MyFile">
               <xs:complexType>
                     <xs:sequence>
                           <xs:element name="FIELD1" type="xs:double"/>
                           <xs:element name="FIELD2" type="xs:string"/>
                           <xs:element name="FIELD3" type="xs:string"/>
                           <xs:element name="FIELD4" type="xs:string"/>
                           <xs:element name="FIELD5" type="xs:string"/>
                           <xs:element name="FIELD6" type="xs:dateTime"/>
                           <xs:element name="GROUP" maxOccurs="unbounded">
                                 <xs:complexType>
                                       <xs:sequence>
                                             <xs:element name="GROUPID" type="xs:string"/>
                                             <xs:element name="GROUPCATEGORY" type="xs:string"/>
                                             <xs:element name="UNIT" maxOccurs="unbounded">
                                                   <xs:complexType>
                                                         <xs:sequence>
                                                               <xs:element name=" UNITNAME" type="xs:string"/>
                                                               <xs:element name="REVIEWER" type="xs:string"/>
                                                               <xs:element name="DATEANDTIME" type="xs:dateTime"/>
                                                               <xs:element name="SUBUNIT" maxOccurs="unbounded">
                                                                     <xs:complexType>
                                                                           <xs:sequence>
                                                                                 <xs:element name=" SUBUNITPRICE" type="xs:string"/>
                                                                           </xs:sequence>
                                                                     </xs:complexType>
                                                               </xs:element>
                                                         </xs:sequence>
                                                         <xs:attribute name="MULTIENTRY" type="xs:string"/>
                                                         <xs:attribute name="PARTIALUNIT" type="xs:string"/>
                                                         <xs:attribute name="KIT" type="xs:string"/>
                                                   </xs:complexType>
                                             </xs:element>
                                       </xs:sequence>
                                       <xs:attribute name="FULL" type="xs:string"/>
                                       <xs:attribute name="VER" type="xs:string"/>
                                 </xs:complexType>
                           </xs:element>
                     </xs:sequence>
                     <xs:attribute name="MyFile" type="xs:string"/>
               </xs:complexType>
         </xs:element>
   </xs:schema>

我的 XML。注释位于 GROUP、UNITNAME 和 SUBUNIT 节点之后，用于引用节点长度、结构和潜在的 XML 文件大小（每个大约 500-600 MB 的文件可能有 100k GROUP 节点）。省略号 (...) 只是标记该特定节点有一个迭代，我希望您能在节点值中找到隐含的迭代次数。在任何给定时间，这些文件中的几个可能必须导入到 SQL Server 数据库中。

<?xml version="1.0" encoding="utf-8"?>
<MyFile xmlns="urn:MyFile-schema">
    <FIELD1>FileName</ FIELD1>
    <FIELD2>Foo</ FIELD2>
    <FIELD3>Bar</ FIELD3>
    <FIELD4>Upload</ FIELD4>
    <FIELD5>UserName</ FIELD5>
    <FIELD6>UploadTime</ FIELD6>
    <GROUP VER="Yes" FULL="false">     ‘<---- There can be up to 100K of these (maybe more soon) ---identified by child node GROUPID
        <GROUPID>GrName1</ GROUPID>   
        <GROUPCATEGORY>MyCategory</ GROUPCATEGORY>
        <UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”> ‘<---- 12 to 35 possible in each GROUP NODE ---identified by child UNITNAME
            <UNITNAME>Unit1</ UNITNAME>                 
            <REVIEWER>UserName</ REVIEWER>
            <DATEANDTIME>DateTime</ DATEANDTIME>
            <SUBUNIT> ‘<---- 2 in each UNIT NODE most of time – will be same tag SUBUNIT
                <SUBUNITPRICE>11.50</ SUBUNITPRICE>
            </ SUBUNIT>
            < SUBUNIT> ‘<-sometimes 3 in intermittent unit nodes but rarely included in file
                < SUBUNITPRICE >20.00</ SUBUNITPRICE>
            </ SUBUNIT>
        </ UNIT>
        ...
        <UNIT KIT=”1” PARTIALUNIT ="false" MULTIENTRY=”Yes”>
            <UNITNAME>Unit23</ UNITNAME> 
            <REVIEWER>UserName</ REVIEWER>
            <DATEANDTIME>DateTime</ DATEANDTIME>
            <SUBUNIT> 
                <SUBUNITPRICE>$11.50</ SUBUNITPRICE>
            </ SUBUNIT>
            < SUBUNIT> 
                <SUBUNITPRICE>$20.00</ SUBUNITPRICE>
            </ SUBUNIT>
        </ UNIT>
    </GROUP>
    <GROUP VER="Yes" FULL="false">
        <GROUPID>GroupName100,000</ GROUPID>   
        ...
    </GROUP>
</MyFile>

这就是我一直在做的事情。

架构导入：

--Import the schema
IF EXISTS(SELECT * FROM sys.xml_schema_collections where [name] = 'XMLSchema')
DROP XML SCHEMA COLLECTION[XMLSchema]

DECLARE @MySchema XML
SET @MySchema = 
(SELECT * FROM OPENROWSET
    (BULK 'C:\Path\Schema.xsd', SINGLE_BLOB) AS xmlData
)
CREATE XML SCHEMA COLLECTION[XMLSchema] AS @MySchema

加载文件。一个 50k 的 GROUP 节点文件大约需要 2 分钟，我不知道 100k 需要多长时间。我想加快速度。我向 xml 列添加了一个索引。我不太确定这部分，除了我把它作为一个建议，它可以加快下游查询的速度，而且我知道它运行。我知道我可以通过编辑掉不必要的属性（所有属性）和字段来节省一些时间和资源，但我发现它只是在前端完成删除它们的工作。

我知道如果我将辅助索引添加到 XML 列，它确实可以加快速度。我不知道从哪里开始。如果有人对参考或快速添加有一些建议，我将不胜感激。

CREATE TABLE XMLTestTable
( 
ID INT IDENTITY PRIMARY KEY,
DATA xml(CONTENT MyXmlSchema)
)

INSERT INTO XMLTestTable
(DATA)

SELECT CONVERT(XML, BulkColumn) as BulkColumn


--import an xml file into the column
FROM OPENROWSET(BULK 'C:\Path\FileName.XML', SINGLE_BLOB) as x

CREATE PRIMARY XML INDEX PXML_DATA 
ON XMLTestTable (DATA)

下一步：我需要将每个 GROUPID 的 GROUPID 和 SUBUNITPRICE 放入一个表中，我将其称为 GROUPTABLE，希望它看起来像这样：

|ID    |GROUPID|UNIT1_SUBUNITPRICE_1|UNIT1_SUBUNITPRICE_2|……|UNIT23_SUBUNITPRICE_2|
|1     |GrName1|11.50               |20.00               |……|25.00                |
|2     |GrName2|1.00                |32.41               |……|45.51                |

所以我创建了表：

CREATE TABLE GROUPTABLE
(
ID int IDENTITY(1,1) PRIMARY KEY,
GROUPID varchar(20),
UNIT1_SUBUNITPRICE_1 varchar(7),
UNIT1_SUBUNITPRICE_2 varchar(7),
…
UNIT23_SUBUNITPRICE_1 varchar(7),
UNIT23_SUBUNITPRICE_2 varchar(7)
)

现在是产生错误的部分！出于演示目的，假设我只将 ID 和 GROUPID 字段添加到此处的表中。如果我一开始只是尝试插入索引和 groupid，效果会很好！像这样：

–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')

INSERT INTO GROUPTABLE
Select
    t.b.value('GROUPID[1]', 'varchar(20)') AS GROUPID 
    FROM XMLTestTable 
CROSS APPLY
    DATA.nodes('//MyFile/GROUP) AS t(b)

...然后是问题。这里假设我只将 ID 和 UNIT1_SUBUNITPRICE_1 添加到表中。让我们尝试添加子单位价格：

–-migrate the data from the xmlcolumn to the table
WITH XMLNAMESPACES(DEFAULT 'urn:MyFile-schema')

INSERT INTO GROUPTABLE

Select 
    t.b.value('UNIT[UNITNAME=“Unit1”]/../SUBUNIT[1]/SUBUNITPRICE[1]', 'varchar(7)') AS SUBUNITPRICE_1
FROM XMLTestTable 
CROSS APPLY
    DATA.nodes('//MyFile/GROUP) AS t(b)

生成我在开头提到的错误。

XQuery [XMLTestTable.DATA.value()]：无法隐式原子化或将“fn:data()”应用于复杂内容元素，在推断类型“元素”中找到类型“xs:anyType”({urn:MyFile-schema}:SUBUNITPRICE ,xs:anyType) *'.

如果你在这漫长的事情中坚持我，我很感激！如果你走到这一步，我的问题是：

1. 查询中的 xpath 表达式我做错了什么？
2. 我可以做些什么来加快将 XML 文件导入到色谱柱中的速度？
3. 我可以做些什么来加快列到表中的迁移？
4. 为每个 UNIT 制作一堆表而不是为所有 UNIT 制作一张表会更好吗？
5. 最后，我听说有人使用 XML 对象并一次解析文件说 10k 行并以这样的过程循环遍历它们（将文件的一部分作为对象变量导入，将其放入表中，重复多次行，然后一次迁移 10k 行）。这可能吗？它会有帮助吗？

2018 年 10 月 30 日更新

至于导入 xml 文件，我尝试了几件事。

1. 我搞砸了使用 xml 变量而不是将其转储到表中，我发现变量加载速度稍微快了一点……在加载 xml 时可能会节省一两秒钟。我还比较了直接加载到表中与将 xml 加载到变量中然后将变量插入到列中。他们花费了相同的时间，或者至少可以忽略不计。我还没有比较查询变量或列是否更快以及索引是否有影响，尽管我读过的所有内容都表明索引（特别是具有更多嵌套 xml 的二级索引）大大减少了加载后查询时间。目前，我在此更新中的测试不涉及变量。我不太确定从这里去哪里，但我有一些想法，如果有的话，我会更新。

2. 不验证模式更快。如果文件已经过验证，或者您可以对数据充满信心，那么这样做就不值得了。我暂时将其从进一步测试中删除。

3. 删除 xml 列上的主键可以更快地加载到列中。由于将数据存储为 xml 的开销太大，而我的目标是将其转换为 RDBMS 形式，这可能也不值得，但我不确定。现在，我也已将其从进一步的测试中删除。

4. 我搞砸了加载 GROUP 父节点数量较少的多个文件，它在加载 xml 方面产生了很大的不同（我还没有看到它如何影响查询和转换时间）。我以增量方式创建了 xml 集，最多 10,000 个 GROUP 节点，然后是 50,000 个 GROUP 节点，然后编写动态 sql 查询以批量导入它们（我将在找到结果后发布查询）。查询将 xml 直接放在 table/xml 列中。

我运行的第一个测试是将直接加载到一个表（一个包含 10,000 个 GROUP 节点的 xml 文件）与加载 10,000 个文件（每个文件有一个 GROUP 节点）进行比较。

10,000 个 GROUP 节点的 1 个文件需要 2 秒才能加载到 1 行

1 GROUP 节点的 10,000 个文件需要 19 秒才能加载到 10,000 行中

我运行的第二个测试是比较不同数量的 50,000 个 GROUP 节点。

5,000 个 GROUP 节点的 10 个文件需要 13 秒才能加载到 10 行中

10,000 个 GROUP 节点的 5 个文件需要 6 秒才能加载到 5 行

50,000 个 GROUP 节点的 1 个文件需要 12 秒才能加载到 1 行

显然，一次 10k 比这里的其他任何东西都要好得多，至少对于 50K GROUP 节点而言。如果我正在加载 100K 个节点，这可能会改变。也许我明天会尝试并报告。

注意：我在右下角使用了查询计时器，所以这里的精度是 1 秒。

这是我用来执行此操作的动态 sql 查询：

--variables for dynsql and loop
DECLARE @i int --I named the files with an integer so I could insert them into the loop
DECLARE @dsql varchar(Max)

--create xml table
CREATE TABLE xTable
(
ID INT IDENTITY PRIMARY KEY,
xData XML NOT NULL
);

--loop dynsql to import xml to xTable
Set @i = '1'
WHILE @i <=1 --I changed this depending on how many files I was loading
BEGIN
Set @dsql = 'INSERT INTO xTable(xData)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn
FROM OPENROWSET(Bulk ' + Char(39) + 'C:\MyPath\' + CAST(@i as nvarchar(5)) + '.xml' + CHAR(39) + ', SINGLE_BLOB) x;'
Exec(@dsql)
SET @i = @i + 1
END;

这对我提出了更多问题，我不是在这里寻找答案，而是将它们写下来供任何跟随者思考。

1. 1 行 50k GROUP 节点和 5 行 10k GROUP 节点之间的 xml 到 RDBMS 转换有什么区别？

2. 将 50k GROUP 节点的 1 个 xml 转换为 10k GROUP 节点的 5 个 xml 需要一段时间（我用 Excel VBA 做到了......我知道我可以使用其他东西，但它是我所拥有和知道的）。我猜最明智的做法可能是做一些 XSLT 工作（这是我需要学习的东西）

3. 在这样做的过程中，我想知道是否有一种更快的方法可以通过 sql server 中的 sql server xml 变量将 xml 解析为 10k 节点片段，并且会比在 sql server 之外进行解析更快（我不知道在哪里从这里开始）。

对于仍在关注的任何人，或者如果您碰巧在此线程上

100 个文件，每个文件有 1,000 个 GROUP 节点 - 25 秒

40 个文件，每个文件有 2,500 个 GROUP 节点 - 24 秒

20 个文件，每个文件有 5,000 个 GROUP 节点 - 24 秒

10 个文件，每个文件有 10,000 个 GROUP 节点 - 24 秒

4 个文件，每个文件有 25,000 个 GROUP 节点 - 20 秒

2 个文件，每个文件有 50,000 个 GROUP 节点 - 22 秒

1 个包含 100,000 个 GROUP 节点的文件 - 22 秒