在包含XML但不存盤為XML型別的列中查詢XMLXPATH

我正在處理的資料庫中有一個包含 XML 的列，但存盤為varchar. 我可以CAST()將列轉換為 XML，但尚不清楚如何使用 XPATH 或其他方式從該列中獲取任何內容。

EXTRACT()似乎只針對 MYSQL，并且.value()在我嘗試過的任何形式下都不起作用。我希望我在這里做錯了。

如果有特定于版本的解決方案，查詢@@VERSION會得到以下資訊：

Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64) Nov 1 2020 00:48:37 著作權所有 (c) Microsoft Corporation Enterprise Edition：Windows NT 6.3 上基于內核的許可（64 位）（內部版本 9600：）（管理程式）

下面是我正在使用的 XML 的簡化示例。任何有關如何提取任何給定元素的值的示例都將不勝感激。

<?xml version="1.0" encoding="UTF-8"?>
<ExampleXML>
   <Version></Version>
   <NameSpace></NameSpace>
   <ClientID></ClientID>
   <ClientName></ClientName>
   <ShipToInfo>
      <Name></Name>
      <Address1></Address1>
      <Address2 />
      <City></City>
      <State></State>
      <Postal></Postal>
      <Country></Country>
      <Phone></Phone>
      <Fax></Fax>
      <EIdType />
      <EId></EId>
   </ShipToInfo>
   <BillToInfo>
      <Name></Name>
      <Address1></Address1>
      <Address2 />
      <City></City>
      <State></State>
      <Postal></Postal>
      <Country></Country>
      <Phone></Phone>
      <Fax></Fax>
      <EIdType />
      <EId></EId>
   </BillToInfo>
</ExampleXML>

編輯：示例.value()

SELECT CAST(CONTENT AS xml).value('(/ExampleXML/ShipToInfo/@EId)[1]', 'int')

結果列包含NULL

編輯2：看起來對我有用的最終版本如下：

SELECT top 1 ET.*, t2.c.value('EId[1]', 'varchar(max)') AS store_num
FROM example_table ET
    OUTER APPLY (SELECT CAST(ET.CONTENT AS xml) AS realxml) t1
    OUTER APPLY t1.realxml.nodes('//ExampleXML/ShipToInfo') AS t2(c);

uj5u.com熱心網友回復：

請嘗試以下解決方案。

對于包含 XML 資料的列，最好使用 XML 資料型別。

它從 SQL Server 2005 開始可用。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Content NVARCHAR(MAX));
INSERT INTO @tbl (Content) VALUES
(N'<ExampleXML>
   <Version></Version>
   <NameSpace></NameSpace>
   <ClientID></ClientID>
   <ClientName></ClientName>
   <ShipToInfo>
      <Name>Paul Braddock</Name>
      <Address1>3851 SE 52nd St.</Address1>
      <Address2 />
      <City>Ottawa</City>
      <State></State>
      <Postal>12345</Postal>
      <Country>Canada</Country>
      <Phone>123-456-7890</Phone>
      <Fax></Fax>
      <EIdType>Alien</EIdType>
      <EId>18</EId>
   </ShipToInfo>
   <BillToInfo>
      <Name></Name>
      <Address1></Address1>
      <Address2 />
      <City></City>
      <State></State>
      <Postal></Postal>
      <Country></Country>
      <Phone></Phone>
      <Fax></Fax>
      <EIdType />
      <EId></EId>
   </BillToInfo>
</ExampleXML>');
-- DDL and sample data population, end

SELECT t.ID
    , x.value('(Name/text())[1]', 'VARCHAR(20)') AS [Name]
    , x.value('(Address1/text())[1]', 'VARCHAR(20)') AS Address1
    , x.value('(City/text())[1]', 'VARCHAR(20)') AS City
    , x.value('(Country/text())[1]', 'VARCHAR(20)') AS Country
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST(content AS XML)) AS t1(c)
CROSS APPLY c.nodes('/ExampleXML/ShipToInfo') AS t2(x);

輸出

 ---- --------------- ------------------ -------- --------- 
| ID |     Name      |     Address1     |  City  | Country |
 ---- --------------- ------------------ -------- --------- 
|  1 | Paul Braddock | 3851 SE 52nd St. | Ottawa | Canada  |
 ---- --------------- ------------------ -------- --------- 

標籤：sql服务器 xml tsql 查询

上一篇：SQLServer按從前到后的順序從記錄中減去一個值

下一篇：T-SQL從XML中獲取值