时间戳

首页 > 解决方案 > 查询 XML 列

sql-server - 查询 XML 列

问题描述

我有一个采用这种格式的 XML 列：

<methodResponse>
  <params>
    <param>
      <value>
        <struct>
          <member>
            <name>SubscriberBalanceBefore</name>
            <value>
              <string>0.0</string>
            </value>
          </member>
          <member>
            <name>TimeStamp</name>
            <value>
              <dateTime.iso8601>20200812T12:31:10</dateTime.iso8601>
            </value>
          </member>
          <member>
            <name>Subsc riberMSISDN</name>
            <value>
              <string>83000000</string>
            </value>
          </member>
          <member>
            <name>TransactionID</name>
            <value>
              <string>8035768</string>
            </value>
          </member>
        </struct>
      </value>
    </param>
  </params>
</methodResponse>

我希望得到如下表所示的结果：

-------------------------
name      | TransactionID
----------|---------------
830000000 | 8035768

我尝试过使用CROSS，OUTER APPLY但它不起作用：

SELECT 
    X.Y.value('(name)[3]', 'VARCHAR(max)') as NAME_FIELD,
    X.Y.value('(value/string)[4]', 'varchar(max)') DATA_CONTENT
FROM [TABLE] T
OUTER APPLY T.xmlIn.nodes('methodResponse/params/param/value/struct/member') as X(Y)

它不会给出错误，但会为两个字段返回空值。有人可以帮忙吗？

标签： sql-serverxml

解决方案

您的单例标识符不正确。例如(name)[3]将引用每个name节点中的第三个节点。每个成员只有 1 个节点，因此值。membernameNULL

您需要分别引用第 3 个和第 4member个节点：

DECLARE @XML xml = '<methodResponse>
  <params>
    <param>
      <value>
        <struct>
          <member>
            <name>SubscriberBalanceBefore</name>
            <value>
              <string>0.0</string>
            </value>
          </member>
          <member>
            <name>TimeStamp</name>
            <value>
              <dateTime.iso8601>20200812T12:31:10</dateTime.iso8601>
            </value>
          </member>
          <member>
            <name>Subsc riberMSISDN</name>
            <value>
              <string>83000000</string>
            </value>
          </member>
          <member>
            <name>TransactionID</name>
            <value>
              <string>8035768</string>
            </value>
          </member>
        </struct>
      </value>
    </param>
  </params>
</methodResponse>'

SELECT X.Y.value('(member[3]/value/string/text())[1]', 'varchar(max)') as NAME_FIELD, --Does this need to be a varchar(MAX)? Why not an int?
       X.Y.value('(member[4]/value/string/text())[1]', 'varchar(max)') DATA_CONTENT --Does this need to be a varchar(MAX)? Why not an int?
FROM (VALUES(@XML))T(xmlIn)
OUTER APPLY T.xmlIn.nodes('methodResponse/params/param/value/struct') as X(Y);

但是请注意，上述解决方案取决于节点的序数位置。使用 XPATH 和一些布尔逻辑，您可以避免这种情况：

SELECT X.Y.value('(member[name/text()="Subsc riberMSISDN"]/value/string/text())[1]', 'int') as NAME_FIELD,
       X.Y.value('(member[name/text()="TransactionID"]/value/string/text())[1]', 'int') DATA_CONTENT
FROM (VALUES(@XML))T(xmlIn)
OUTER APPLY T.xmlIn.nodes('methodResponse/params/param/value/struct') as X(Y);

推荐阅读