主页 / dba / 问题

问题[xml](dba)

Martin Hope
Fatdrfrog
Asked: 2021-11-23 03:35:06 +0800 CST
  • 0

通过以下问题：查看 XML 元素是否存在于文档中具有特定值的任何级别

是否有可能的解决方案来实施like条款exist？

例如，我有类似的 XML 结构，但在 "tag" 中：<ContactLName>somevalues;Brandt;anothervalues</ContactLName> "Brandt" 存储有点不同。要找到“Btandt”，我必须使用类似的东西like，但我什么也没找到。
这是我正在寻找的示例：
WHERE tmp.[XmlCol].exist(N'/*/ContactLName[text() like "%Brandt%"]') = 1;

编辑（XML）： 
<event>
<id>23456</id>
<status_in_knowledge_map>active</status_in_knowledge_map>
<custom_elems>
    <custom_elem>
        <name>f_n84u</name>
        <value>6769747818850362088;6622044220776915827;5370802024842340642;6583457400600680603;6724970265512462551;6954452686001110038;6667448592984922570;6390613356250088198;6985638718331227440;5370802050233479119;5370802024842340639;6208275408034531857;6078654416541145539;6585528811993904287;6805915353243667957;6842010739999728536;6964839747571514100;6491764192266708067;6630060828301937234;6964682936739957053;6996382008379854236;5703859111941721724;6780468964682252783;</value>
    </custom_elem>
    <custom_elem>
        <name>f_twxr</name>
        <value>7031439420534698908;5667697629116916565;5667697629116916521;7032219545122506576;7032219609944822365;7032219012824308287;7032219395780000352</value>
    </custom_elem>
</custom_elems>
<asd>adsf</asd>
sql-server xml
Martin Hope
FlanMan
Asked: 2020-11-15 06:40:04 +0800 CST
  • 6

在此 SQL Server OPENXML 示例中：

declare @xmldata xml    

set @xmldata = 
'<?xml version="1.0"?>
<wddxPacket version="1.0">
    <data>
        <var dimension="task"><string>Task 1</string></var>
        <var dimension="startdate"><string>2020-11-14</string></var>
    </data>
    <data>
        <var dimension="startdate"><string>2020-11-15</string></var>
        <var dimension="task"><string>Task 2</string></var>
    </data>
</wddxPacket>'

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata

SELECT *
FROM OPENXML(@hDoc, '//wddxPacket/data',2)
        WITH(
            task          varchar(200) 'var[1]/string',
            startdate     varchar(200) 'var[2]/string'
    )

EXEC sp_xml_removedocument @hDoc

这是当前的输出：

task         startdate
------       ----------
Task 1       2020-11-14
2020-11-15   Task 2

但我真的想要这个输出：

task         startdate
------       ----------
Task 1       2020-11-14
Task 2       2020-11-15

为了识别内容，我不能单独使用“var”，因为所有感兴趣的元素都被命名为“var”。我不能使用位置 [1] 或 [2]，因为位置编号在 XML 中不一致，导致当前混淆输出。如何根据“var”和“task”或“startdate”的“dimension”属性名称识别元素？

sql-server xml
Martin Hope
user1664043
Asked: 2020-07-22 13:20:08 +0800 CST
  • 3

我从供应商处获取这些 xml 文件，它是 NITF（新闻）模式和http://www.xmlnews.org/namespaces/meta#新闻元数据模式（来自 Space 1999！）的包装器

不幸的是，它们根本没有在外部文档上声明任何命名空间。这是他们给我们的：

<?xml version="1.0"?>
<document>
<nitf>
    <head>...</head>
    <body>...</body>
    etc
</nitf>
<xn:Resource xmlns:xn="http://www.xmlnews.org/namespaces/meta#">...</xn:Resource>
</document>

我试图查看是否可以通过创建 xml 模式集合并对其进行解析来提高吞吐量，但是 xml 文本中缺少任何命名空间声明让我感到困惑。

我试过把

;WITH XMLNAMESPACES (default 'http://iptc.org/std/NITF/2006-10-18/')
SELECT CAST(rawXml as XML(NitfSchemaCollection))

但它不喜欢它（XML Validation: Declaration not found for element 'document' exception）。

我什至尝试使用 ;WITH XMLNAMESPACES 将原始 xml 解析为 XML 类型，然后将其转换为 XML(NitfSchemaCollection)，但同样的问题。

所以我的问题是：

  1. 除了重写来自供应商的传入 xml 文档之外，有什么方法可以将 xml 模式集合应用于解析？

和

  1. 解析类型会产生足够的性能增强以使其进一步追求吗？

我们目前使用的是 Sql Server 2008 sp4，但如果这可能会改变某些东西，我可以在更新的实例上尝试它。

编辑：这是一个示例文档。nitf和xn:Resource节点都符合两个非常古老的新闻专线服务序列化标准。对于我的模式集合，我添加了两者，并调整了 nitf 以添加非标准的文档节点。该架构对于帖子来说很长，但如果有人感兴趣，我可以添加它们。

<?xml version="1.0"?>
<document>
<nitf>
<head>
    <title>First World Problems: 'Should I cancel my Easter holiday and charter a superyacht to escape coronavirus?'</title>
</head>
<body>
    <body.head>
        <hedline>
        <hl1>First World Problems: 'Should I cancel my Easter holiday and charter a superyacht to escape coronavirus?'</hl1>
        </hedline>
        <byline>
        <bytag>By Caroline White</bytag>
        </byline>
        <distributor>Telegraph Group</distributor>
    </body.head>
    <body.content>
    <p><em>'I am thinking of cancelling my Easter holiday and chartering a yacht to whisk my immediate family off to sea. The idea is that we can still enjoy the trip of a lifetime without risking contracting the coronavirus. How would you recommend proceeding?'</em></p>
    <p>If you’ve got the wallet for it, a superyacht charter offers the most luxurious seclusion on the planet – and like the hand sanitiser aisle in Boots, you’re not the first to think of it. Some brokers anticipate an uptick in superyacht sales, as UHNWI look to create safe havens, and wealthy holidaymakers are likely to follow suit. So get moving.</p>
    <p>The first step is to recruit a charter broker – try Fraser, Burgess, YPI or <org value="ACORN:3601037911" idsrc="xmltag.org" >Camper &amp; Nicholsons</org>. They will gauge your budget, preferences and read your personality (are you too formal for that laid-back Aussie captain; are you too wild for that silver-service English crew) then come back to you with a bespoke selection of options. The next step is a rather blissful journey through yacht brochures. Then there are the itineraries to flick through: beach barbeques, diving days and suppers under the stars…</p>
    ...blah blah blah...
    <p><em><em>If you have a question for any of our Telegraph Luxury experts, on any topic, please email <a href="http://mailto:[email protected]/">[email protected]</a></em></em></p>
    <p><em>Last week on First World Problems</em></p>
    <p><a href="https://www.telegraph.co.uk/luxury/womens-style/first-world-problems-expensive-blonde-highlights-mayfair-salon/">First World Problems: 'Are expensive highlights at a Mayfair salon worth the price-and the journey?'</a></p>
    <p><em><em>Sign up for the <a href="https://www.telegraph.co.uk/newsletters/Luxury/">Telegraph Luxury newsletter</a> for your weekly dose of exquisite taste and expert opinion.</em></em></p>
</body.content>
</body>
</nitf>
<xn:Resource xmlns:xn="http://www.xmlnews.org/namespaces/meta#">
    <xn:providerName>Telegraph Group</xn:providerName>
    <xn:providerCode>127</xn:providerCode>
    <xn:serviceName>Telegraph Online</xn:serviceName>
    <xn:serviceCode>2</xn:serviceCode>
    <xn:resourceID>202003100715TELEGR__ONLINE___60979152</xn:resourceID>
    <xn:publicationTime>2020-03-10T07:15:00-04:00</xn:publicationTime>
    <xn:receivedTime>2020-03-10T07:50:43-04:00</xn:receivedTime>
    <xn:title>First World Problems: 'Should I cancel my Easter holiday and charter a superyacht to escape coronavirus?'</xn:title>
    <xn:rendition>202003100715TELEGR__ONLINE___60979152.xml</xn:rendition>
    <xn:vendorData>WAVO:Publish Reason=CORRECTED</xn:vendorData>
    <xn:vendorData>WAVO:alert=FALSE</xn:vendorData>
    <xn:vendorData>WAVO:headline_only=FALSE</xn:vendorData>
    <xn:vendorData>WAVO:temporary=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Publish Reason=CORRECTED</xn:vendorData>
    <xn:vendorData>AMX:Alert=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Headline Only=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Temporary=FALSE</xn:vendorData>
    <xn:vendorData>AMX:Special Code=PS/p.TELEGR__</xn:vendorData>
    <xn:vendorData>AMX:Special Code=PS/s.ONLINE__</xn:vendorData>
    <xn:copyright>Copyright © 2020 Telegraph.co.ukk. All rights reserved</xn:copyright>
    <!-- Entity Extractor -->
    <xn:companyCode>ACORN:A.3601037911#6#60#60</xn:companyCode>
    <xn:companyCode>ACORN:A.2295203068#6#60#60</xn:companyCode>
    <xn:industryCode>IC/fini#6#50#60</xn:industryCode>
    <xn:industryCode>IC/fini.bank#6#60#60</xn:industryCode>
    <xn:industryCode>IC/fini.invs#6#60#60</xn:industryCode>
    <xn:industryCode>IC/fini.secr#6#60#60</xn:industryCode>
    <xn:industryCode>IC/svcs#6#50#60</xn:industryCode>
    <xn:industryCode>IC/svcs.prof#6#60#60</xn:industryCode>
    <xn:locationCode>LB/car#7#70#49</xn:locationCode>
    <xn:locationCode>LR/car#9#70#90</xn:locationCode>
    <xn:locationCode>LU/car#9#70#90</xn:locationCode>
    <xn:locationCode>LU/car.any#7#49#70</xn:locationCode>
    <xn:subjectCode>NZ/COID#6#50#60</xn:subjectCode>
    <xn:subjectCode>NZ/COID.1475554280#6#60#60</xn:subjectCode>
    <xn:subjectCode>NZ/COID.27088#6#60#60</xn:subjectCode>
    <xn:subjectCode>NZ/COID.5838940#6#60#60</xn:subjectCode>
    <!-- Classifier -->
    <xn:subjectCode>IS/lifesoc.privair#5#50#50</xn:subjectCode>
    <xn:subjectCode>MC/HOT#6</xn:subjectCode>
    <xn:subjectCode>NC/67115358#9#98#50</xn:subjectCode>
    <xn:subjectCode>NC/67115586#5#55#50</xn:subjectCode>
    <xn:subjectCode>NC/67119129#5#58#50</xn:subjectCode>
    <xn:subjectCode>NC/67119169#5#50#50</xn:subjectCode>
    <xn:vendorData>AMX:Special Code=PT/updated</xn:vendorData>
    <xn:subjectCode>XC/any#6#50#60</xn:subjectCode>
    <xn:subjectCode>XC/any.company#6#60#50</xn:subjectCode>
    <xn:subjectCode>XC/Private#6#60#50</xn:subjectCode>
    <!-- Rules -->
    <xn:subjectCode>MC/BIZREL#1</xn:subjectCode>
    <xn:subjectCode>NE/BAYERINS#5#58#50</xn:subjectCode>
    <xn:subjectCode>NE/GEOAMER#9#70#90</xn:subjectCode>
    <xn:subjectCode>NE/GEOCARIB#9#70#90</xn:subjectCode>
    <xn:industryCode>NI/Banks#6#60#60</xn:industryCode>
    <xn:industryCode>NI/Finance#6#60#60</xn:industryCode>
    <xn:industryCode>NI/Securities#6#60#60</xn:industryCode>
    <xn:industryCode>NI/Services#6#60#60</xn:industryCode>
    <xn:vendorData>AMX:Special Code=TL/americas#7#70#50</xn:vendorData>
    <xn:vendorData>AMX:Special Code=TL/LOC#7#50#70</xn:vendorData>
    <xn:vendorData>AMX:Special Code=TT/TOPIC#5#50#50</xn:vendorData>
    <xn:vendorData>AMX:Special Code=TT/transport#5#50#50</xn:vendorData>
    <xn:language>en</xn:language>
</xn:Resource>
</document>

我们的处理必须解析这些文档，然后我们尝试将一些元数据属性标准化为各种表和列。

只是解析未知的 xml，我认为 Sql Server 必须为每个解析的文档从一个空白名称表开始；我认为键入的 xml 列以已知词汇表开头，应该更快。另外希望 xquery 也会更快。

以下是我们在处理过程中执行的查询示例：

;WITH XMLNAMESPACES ('http://www.xmlnews.org/namespaces/meta#' AS xn)
Insert Into dbo.NewsStory
Select NewsID,provider,service,
    CASE When provider='AMSPIDER' and Service='ACBJ' and PublicationAbbrev='web.site' Then dbo.fnGetSpiderPubAbbrev(PublicationAbbrev_Spider) Else PublicationAbbrev End As PublicationAbbrev,
    Title, PublishDate, AMXReceivedTime, AllowedReleaseTime,ParsedDate,DateLine, Description, [Language], PublishReason, IsAlert, IsHeadLine, IsTemporary, Copyright
    From (
        Select  X.NewsID, 
            replace(RIGHT(RS.c.value('(./xn:vendorData[substring((./text())[1],1,22)="AMX:Special Code=PS/p."]/text())[1]', 'VARCHAR(50)'),8)   , '_', '') as provider, 
            replace(RIGHT(RS.c.value('(./xn:vendorData[substring((./text())[1],1,22)="AMX:Special Code=PS/s."]/text())[1]', 'VARCHAR(50)'),8)   , '_', '') as service,
             CONVERT(NVARCHAR(max),RS.c.query('xn:vendorData'))) as PublicationAbbrev,
            replace(RS.c.value('(./xn:vendorData[substring((./text())[1],1,11)="AMX:Credit="]/text())[1]', 'VARCHAR(200)'),'AMX:Credit=', '')  as PublicationAbbrev_Spider,
            RS.c.value('(./xn:title/text())[1]', 'VARCHAR(200)') AS Title,
      CONVERT(DATETIME,REPLACE(LEFT(RS.c.value('(./xn:publicationTime/text())[1]', 'VARCHAR(50)'),19),'T',' ')) AS PublishDate,
        CONVERT(DATETIME,REPLACE(LEFT(RS.c.value('(./xn:receivedTime/text())[1]', 'VARCHAR(50)'),19),'T',' ')) AS AMXReceivedTime,
        CONVERT(DATETIME,REPLACE(LEFT(RS.c.value('(./xn:releaseTime/text())[1]', 'VARCHAR(50)'),19),'T',' ')) AS AllowedReleaseTime, getdate() as ParsedDate,
        RS.c.value('(./xn:dateline/text())[1]', 'VARCHAR(200)') AS DateLine,
        RS.c.value('(./xn:description/text())[1]', 'VARCHAR(2000)') AS Description,
        RS.c.value('(./xn:language/text())[1]', 'VARCHAR(10)') AS [Language],
        LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((.)[1],1,19)="AMX:Publish Reason="])[1]','VARCHAR(45)'),20,25)) AS PublishReason,
       CASE LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((./text())[1],1,10)="AMX:Alert="]/text())[1]','VARCHAR(45)'),11,10)) WHEN 'FALSE' THEN 0 ELSE 1 END AS IsAlert,
       CASE LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((./text())[1],1,18)="AMX:Headline Only="]/text())[1]','VARCHAR(45)'),19,10)) WHEN 'FALSE' THEN 0 ELSE 1 END AS IsHeadLine,
       CASE LTRIM(SUBSTRING(RS.c.value('(./xn:vendorData[substring((./text())[1],1,14)="AMX:Temporary="]/text())[1]','VARCHAR(45)'),15,10)) WHEN 'FALSE' THEN 0 ELSE 1 END AS IsTemporary,
       RS.c.value('(./xn:copyright/text())[1]', 'VARCHAR(1000)')AS Copyright
       From @XmlFileTable X CROSS APPLY AMXFile.nodes('/document/xn:Resource') RS(c)
    ) A

架构集合来自 NITF 源 ( https://www.iptc.org/std/NITF/3.6/specification/nitf-3-6.xsd ) 和 xmlnews dtd ( http://www.xmlnews.org/ dtds/xmlnews-meta-dtd.zip )。

我使用 Visual Studio 将 xmlnews dtd 转换为模式并使用它来播种 NitfSchemaCollection。

然后我将 NITF 模式调整为

  • 删除包含（显然是我不需要的 Ruby 的一个小子集）

  • 添加到标题

     ... xmlns:xn="http://www.xmlnews.org/namespaces/meta#">

    <import namespace="http://www.xmlnews.org/namespaces/meta#" />

  • 在nitf元素声明上方添加了一个文档元素，以匹配供应商发送给我们的内容。例如

    <element name="document"> <complexType> <sequence> <element ref="nitf:nitf" minOccurs="1" maxOccurs="1" /> <element ref="xn:Resource" minOccurs="1" maxOccurs ="1" /> </sequence> </complexType> </element>

每个文档只有 1 个nitf节点和 1 个xn:Resource节点，但 xn:Resource 下的子节点可以有很多实例。

sql-server xml
Martin Hope
Lennart - Slava Ukraini
Asked: 2020-04-16 22:02:19 +0800 CST
  • 0

编辑：我通过将属性与元素混淆来证明我的无知，我更新了我的问题。对不起。为了澄清，我正在寻找元素代码的存在：

<conditionmessage:code>MYCODE</conditionmessage:code>

我有一堆模式，其中有一个包含 XML 列的表。每列都有一些 XML 索引。我很确定 SQL 模式之间的 XML 模式不同，但是，每个模式中都存在相同的 XML 索引。我怀疑复制/粘贴，我似乎无法找到 XML 模式的任何文档。所以我想到了一种更实用的方法，即遍历 XML 列并检查每个元素是否存在。这些表格已经存在了一段时间，所以现在所有可能的元素都应该在那里。

现在的问题是，无论价值如何，检查元素是否存在的最佳方法是什么？不幸的是，我的大脑与 XPATH/XQUERY 不兼容，每次我都必须努力让自己相信查询完成了它应该做的事情。我目前的方法似乎有效，但我很好奇是否有更好的方法。最小表的基数约为 100.000 行，最大的表约为 100.000.000 行：

import ibm_db
import ibm_db_dbi
...
conn = ibm_db_dbi.connect("...")
c1 = conn.cursor()

for schema in [ "S1", "S2", ... ]:
    for attribute in [ "a1", "s2", ...]:
        sql = f"""
            select * from {schema}.ATOM_ENTRY
            WHERE XMLEXISTS('$d//*:{attribute}[fn:matches(text(), $p, "i")]' 
                PASSING ENTRY_XML AS "d", CAST('\S*' AS VARCHAR(128)) as "p")
            fetch first 1 rows only
            optimize for 1 rows"""
        c1.execute(sql)
        exists = False
        for row in c1.fetchall():
            exists = True
        print(f"{schema}.ATOM_ENTRY -> {attribute} : {exists}")
db2 xml
Martin Hope
LP.
Asked: 2020-04-04 05:10:02 +0800 CST
  • 2

语境：

我在命令提示符下使用此命令在 XML（包括数据和模式）中创建了一个 sql 表的转储。

BCP "SELECT * FROM Database.Schema.TableName FOR XML AUTO, XMLSCHEMA" queryout  C:\data.xml -w -r "" -S localhost -T

现在我已经配备了包含我需要的所有信息的 XML 文件，我想将它加载到另一个数据库中。

由于将这个表转储创建为 xml 文件很容易，我想必须有一种简单的方法将 XML 文件加载到数据库中（创建表然后加载数据）。有谁知道如何做到这一点？

免责声明：我不是 DBA，这是我第一次使用 BCP，文档正在讨论创建格式文件，但它似乎对我不起作用。

提前感谢您的回复

sql-server xml
Martin Hope
Dan
Asked: 2020-03-20 08:33:01 +0800 CST
  • 0

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

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)
sql-server-2008 xml
Martin Hope
Cory Baumer
Asked: 2020-02-08 19:28:01 +0800 CST
  • 0

我有一个名为product_variants的表，其中包含一个整数列和一个包含一组外键值的 xml 列。

如果我跑了，

select product_id, options_xml from product_variants

这将是一个典型的结果

product_id  |  options_xml
132         |  <options><option_id>1</option_id><option_id>8</option_id></options>

我想以某种方式为每个 option_id 选择一个新行。

我想要的结果，

product_id  |  option_id
132         |  1
132         |  8

先感谢您！

postgresql xml
Martin Hope
david
Asked: 2020-01-11 01:57:44 +0800 CST
  • 1

我想我快到了，但已经陷入僵局！

我有一个相对简单的 XML 结构，我需要将其解析为 SQL 结果集，但似乎无法使其工作。

这是代码：

DECLARE @xml XML;
SET @xml = '
<values>
    <Nationality>
        <item>
            <key>UK</key>
            <value>def</value>
        </item>
        <item>
            <key>IE</key>
            <value>def</value>
        </item>
    </Nationality>
    <Currency>
        <item>
            <key>GBP</key>
            <value>123</value>
        </item>
        <item>
            <key>EUR</key>
            <value>345</value>
        </item>
    </Currency>
</values>';

select I.X.value('(/key/text())[1]', 'int') as ItemID,
       I.X.value('(/value/text())[1]', 'int') as Store
from @XML.nodes('/values/Nationality') as O(X)
cross apply O.X.nodes('/item') as I(X);

本质上，我想从国籍中提取价值：

Key  | Value
-------------
UK   | Def
IE   | Def

我可能想稍后再做货币，但我认为如果我破解了上述问题，那么剩下的就足够简单了。

你能帮忙吗？

t-sql xml
Martin Hope
Lava Viperidae
Asked: 2019-10-09 15:19:48 +0800 CST
  • 1

我有一个名为Warehouses的数据库表，其中包含与特定仓库相关的数据行。该表中的一列名为itemStats并包含以下格式的 XML 数据：

<itemStats xmlns="http://www.blahblahblah.com/blah">
  <itemCounts>
    <item>
      <name>Toaster Oven Device</name>
      <count>6</count>
    </item>
    <item>
      <name>Washing Machine</name>
      <count>2</count>
    </item>
    <item>
      <name>Microwave Oven</name>
      <count>4</count>
    </item>
  </itemCounts>

我想查询表以检查每一行中的该列是否存在某个字符串（例如：Oven），如果找到它，则返回与其关联的计数。因此，如果我在Warehouses 中的给定行的itemStats中有上述数据，我希望它返回以下内容以及来自其他行的其他匹配项：

Name                Count
=========================
Toaster Oven Device     6
Microwave Oven          4

我尝试使用 SQL value() 和 nodes() 多次，但无法生成所需的结果。我知道下面查询中的[1]是错误的，但我不确定如何引用动态位置：

;WITH XMLNAMESPACES (N'http://www.blahblahblah.com/blah' as X)
SELECT itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:name)[1]', 'nvarchar(max)') as Name,
       itemStats.value('(/X:itemStats/X:itemCounts/X:item/X:count)[1]', 'int') as Count
FROM Warehouses
WHERE itemStats.exist('(/X:itemStats/X:itemCounts/X:item/X:name[contains(., "Oven")])') = 1
sql-server-2012 xml