连接不同地理区域的数据库的最佳实践

Question

Dan

Asked: 2020-03-20 08:33:01 +0800 CST2020-03-20 08:33:01 +0800 CST 2020-03-20 08:33:01 +0800 CST

SQL Server 中的 XQuery 将 XML 列数据转换为关系数据表

772

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

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 个回答

Voted

David Browne - Microsoft · Answer 1 · 2020-03-20T11:52:17+08:00

模式是每个都cross apply获取父级的相对位置。尝试这样的事情：

declare @doc xml =N'<?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>'

drop table if exists #xData;

with q as
(
    select 1 ID, @doc xData
    union all 
    select 1 ID, @doc xData
)
select *
into #xData
from q

SELECT Sample.n.value('(SampleID)[1]', 'varchar(20)') AS SampleID,
       Location.n.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName,
       Location.n.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1,
       Location.n.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2

FROM #xData x
cross apply x.xData.nodes('/MyXMLFile/Sample') AS Sample(n)
cross apply Sample.n.nodes('Location') as Location(n)

输出

SampleID             LocationName Foo1   Foo2
-------------------- ------------ ------ ------
0000001A             Jeff         10     11
0000001A             Jenn         11     12
0000002A             Greg         13     14
0000002A             Anne         14     16
0000001A             Jeff         10     11
0000001A             Jenn         11     12
0000002A             Greg         13     14
0000002A             Anne         14     16

(8 rows affected)

SQL Server 中的 XQuery 将 XML 列数据转换为关系数据表

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

SQL Server 中的 XQuery 将 XML 列数据转换为关系数据表

1 个回答

相关问题