时间戳

首页 > 解决方案 > 从 SQL Server 2016+ 加入 XML 列

sql-server - 从 SQL Server 2016+ 加入 XML 列

问题描述

我有一个包含此 XMLTABLE_A的 XML 列：mycategoryXML

<mainxml>
     <category id="1" ftype="1"/>
     <category id="2" ftype="1"/>
     <category id="3" ftype="1"/>
</mainxml>

我还有另一个TABLE_B名为 XML 的列mymappedids

<mapids>
    <ids>
        <myid id="1" name="Category Name1"/>
        <myid id="2" name="Category Name2"/>
        <myid id="3" name="Category Name3"/>
        <myid id="4" name="Category Name4"/>
        <myid id="5" name="Category Name5"/>
        <myid id="6" name="Category Name6"/>
        <myid id="7" name="Category Name7"/>
        <myid id="8" name="Category Name8"/>
        <myid id="9" name="Category Name9"/>
    </ids>
</mapids>

是否可以从中选择所有Category节点TABLE_A以及每个类别的正确名称table_B，所以我最终得到以下结果：

      id | categoryid | categoryname
      ---+------------+-------------
      1  |    1       | categoryname1
      2  |    2       | categoryname2
      3  |    3       | categoryname3

来自 SQL Server 的一个查询？

标签： sql-serverxmltsql

解决方案

有几种方法，但您没有告诉我们足够的背景信息。

特别不清楚：这两个表a和b之间是如何联系的？

你可以试试这个：

模拟您的问题的模型场景（请在下一个问题中自己提供）：

DECLARE @tblA TABLE(aId INT,Xml1 XML);
DECLARE @tblB TABLE(bId INT,Xml2 XML);

INSERT INTO @tblA(aId,Xml1) VALUES
(100
,N'<mainxml>
     <category id="1" ftype="1"/>
     <category id="2" ftype="1"/>
     <category id="3" ftype="1"/>
</mainxml>');
INSERT INTO @tblB(bId,Xml2) VALUES
(200
,N'<mapids>
    <ids>
        <myid id="1" name="Category Name1"/>
        <myid id="2" name="Category Name2"/>
        <myid id="3" name="Category Name3"/>
        <myid id="4" name="Category Name4"/>
        <myid id="5" name="Category Name5"/>
        <myid id="6" name="Category Name6"/>
        <myid id="7" name="Category Name7"/>
        <myid id="8" name="Category Name8"/>
        <myid id="9" name="Category Name9"/>
    </ids>
</mapids>');

--查询

SELECT a.aId
      ,aXmlValues.* 
      ,(SELECT Xml2.value('(/mapids
                            /ids
                            /myid[@id=sql:column("aXmlValues.CategoryId")]
                            /@name)[1]','nvarchar(max)') 
        FROM @tblB b
        WHERE bID=200 /*however you find this...*/) AS CategoryName
FROM @tblA a
CROSS APPLY a.Xml1.nodes('/mainxml/category') aXml(c)
CROSS APPLY(SELECT aXml.c.value('@id','int') AS CategoryId
                  ,aXml.c.value('@ftype','int') AS CategoryType) aXmlValues;

简而言之：

  • 我们使用APPLY ... .nodes()在单独的行（派生集）中获取表“a”的类别。
  • 现在我们可以APPLY再次使用。这是将 XML 的值作为普通列放入结果集中的技巧。
  • 可以使用sql:column().

推荐阅读