0

I am trying to extract value from xml in table.PFB sample xml.

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfKeyValue xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <KeyValue>
        <Key>[#Id#]</Key>
        <Value>354005</Value>
</ArrayOfKeyValue>

I am trying below script and it is not working.

SELECT 
    CAST(CAST(([KeyWordValues]) AS ntext) AS XML).value('(/ArrayOfKeyValue/KeyValue[1])', 'nvarchar(max)')
FROM 
    Table

I'm getting this error :

Msg 2389, Level 16, State 1, Line 1
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

Please note XML is saved as varchar in the database.

Please help. Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Erma
  • 337
  • 1
  • 6
  • 14
  • Does this answer your question? [XQuery \[value()\]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic \*'](https://stackoverflow.com/questions/19940566/xquery-value-value-requires-a-singleton-or-empty-sequence-found-oper) – Danieboy Sep 04 '20 at 05:17
  • Did you try searching for the error first? https://stackoverflow.com/questions/19940566/xquery-value-value-requires-a-singleton-or-empty-sequence-found-oper – Danieboy Sep 04 '20 at 05:18
  • update your path `(ArrayOfKeyValue/KeyValue/Key)[1]`, check this once https://stackoverflow.com/questions/63285429/how-do-i-extract-a-value-from-an-xml-column-in-sql-server/63286171#answers-header – Sowmyadhar Gourishetty Sep 04 '20 at 05:27

1 Answers1

0

Thanks alot for help. I got it working.

 SELECT cast(cast(([KeyWordValues]) as ntext) as 
 xml).value('(/ArrayOfKeyValue/KeyValue/Value)[1]', 'nvarchar(max)')
 FROM Table
Erma
  • 337
  • 1
  • 6
  • 14