Query XML XPATH in column containing XML but not stored as XML type-CodePudding

I have a column in a database I'm working on that contains XML, but is stored as varchar. I can CAST() the column to XML, but it's not clear how I would fetch anything from this column using XPATH or otherwise.

EXTRACT() seems specific to MYSQL only, and .value() doesn't work in any form I've tried it. I'm hoping I'm just doing it wrong here.

In case there's a version-specific solution, querying @@VERSION gets me the following:

Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64) Nov 1 2020 00:48:37 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

A simplified example of the XML I'm working with is below. Any examples on how to extract the values for any given element would be greatly appreciated.

<?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>

Edit: Example of .value()

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

The resulting column contains NULL

Edit 2: It looks like the final version that worked for me is as follows:

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);

CodePudding user response：

Please try the following solution.

It is better to use XML data type for a column with XML data.

It is available starting from SQL Server 2005 onwards.

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);

Output

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