-2

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);
David Metcalfe
  • 2,237
  • 1
  • 31
  • 44
  • 1
    @DaleK Added information – David Metcalfe May 10 '22 at 20:13
  • Does this answer your question? [How to query for Xml values and attributes from table in SQL Server?](https://stackoverflow.com/questions/19165213/how-to-query-for-xml-values-and-attributes-from-table-in-sql-server) – Stu May 10 '22 at 20:14
  • Also see [https://stackoverflow.com/questions/72191278/t-sql-get-value-from-xml](https://stackoverflow.com/questions/72191278/t-sql-get-value-from-xml) – Stu May 10 '22 at 20:18
  • @Stu Doesn't appear to: `Cannot find either column "m" or the user-defined function or aggregate "m.c.value", or the name is ambiguous.` – David Metcalfe May 10 '22 at 20:18
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky May 10 '22 at 21:42
  • 1
    Well `/ExampleXML/ShipToInfo/@EId` is looking for an `EId` attribute on the `ShipToInfo` element, e.g.: ``. Have you tried `/ExampleXML/ShipToInfo/EId` instead? – AlwaysLearning May 10 '22 at 21:51
  • There are lots of problems with the **Edit 2:** T-SQL. – Yitzhak Khabinsky May 10 '22 at 22:54
  • @YitzhakKhabinsky If you can produce a better version, please feel free to edit your answer. It's the only way I could get it to work. – David Metcalfe May 10 '22 at 22:56
  • `//` is slow, also why store your data in `varchar` rather than `xml` in the first place – Charlieface May 10 '22 at 23:34
  • @Charlieface It's not my database. I don't have control over how it's stored. – David Metcalfe May 11 '22 at 15:36

1 Answers1

1

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  |
+----+---------------+------------------+--------+---------+
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21