SQL从XML字段获取数据

Question

我希望格式化 XML 列中的数据。我搜索了 stackoverflow 并找到了一些很好的例子。

来源:CONVERTING SQL NVARCHAR(MAX) TO XML and getting a Value from XML string

我的查询如下:

SELECT
    b.x.value('/CustomDataResponse[1]/FieldData[1]/FieldData[1]/FieldName[1]', 'varchar(100)') as Name
    ,b.x.value('/CustomDataResponse[1]/FieldData[1]/FieldData[1]/FieldDataValue[1]', 'varchar(100)') as Value

FROM
    MyTable a 
    CROSS APPLY (
                  SELECT
                   CAST(CAST ([XMLVAL] AS NVARCHAR(MAX)) AS XML) x
                ) b;

XMl 如下所示:

<CustomDataResponse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <AccountID>0</AccountID>
  <UserNumber xsi:nil="true" />
  <Workstation xsi:nil="true" />
  <SerialNumber xsi:nil="true" />
  <Set>0</Set>
  <SecurityID>0000000</SecurityID>
  <SecurityType>Equity</SecurityType>
  <FieldData>
    <FieldData>
      <FieldName>ISSUER</FieldName>
      <FieldDataValue>ABC INC</FieldDataValue>
    </FieldData>
    <FieldData>
      <FieldName>TICKER</FieldName>
      <FieldDataValue>ABC</FieldDataValue>
    </FieldData>
    <FieldData>
      <FieldName>SECURITY_TYP</FieldName>
      <FieldDataValue>PUBLIC</FieldDataValue>
    </FieldData>
  </FieldData>
</CustomDataResponse>

通过这个查询和 xml，我得到的结果为

如何扩展它以使用其他字段，例如 Ticker、Security_typ ？感谢您的帮助。

Answer 1

一种方法是

SELECT Issuer = c.x.value('FieldData[FieldName[. = ''ISSUER'']][1]/FieldDataValue[1]'
                         ,'varchar(100)'),
       Ticker = c.x.value('FieldData[FieldName[. = ''TICKER'']][1]/FieldDataValue[1]'
                         ,'varchar(100)'),
       SecurityType = c.x.value('FieldData[FieldName[. = ''SECURITY_TYP'']][1]/FieldDataValue[1]'
                         ,'varchar(100)')
FROM   MyTable a
       CROSS APPLY (SELECT CAST(CAST ([XMLVAL] AS NVARCHAR(MAX)) AS XML) x) b
       CROSS APPLY b.x.nodes('/CustomDataResponse[1]/FieldData[1]') c(x);  

顺便问一下，为什么要将 XMLVAL 中间转换为 NVARCHAR(MAX)？