Extract node value from an xml from a table using SQL

Question

<Recording xmlns="http://www.m5net.com/test/configuration/connectors" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <Id>16607</Id>
  <Mode>1</Mode>
  <Enable>true</Enable>
  <Notification i:nil="true" />
  <Notify>false</Notify>
</Recording>

I need to extract the value of mode . This is config column from a ModeConfiguration table .

I have tried

SELECT
config.Value('(/Recording//Mode)[1]', 'varchar(max)') as int
FROM ModeConfiguration

but looks like the namespace is not getting considered correctly .

corrected the description : SELECT config.Value('(/Recording//Mode)[1]', 'varchar(max)') as int FROM ModeConfiguration — user11403920
– user11403920, Commented Dec 18, 2020 at 19:58

Yitzhak Khabinsky · Accepted Answer · 2020-12-20 00:53:03Z

2

The default namespace should be taken into account.

SQL

-- DDL and sample data population, start
DECLARE @ModeConfiguration TABLE (ID INT IDENTITY PRIMARY KEY, config XML);
INSERT INTO @ModeConfiguration (config) VALUES
(N'<Recording xmlns="http://www.m5net.com/test/configuration/connectors"
           xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Id>16607</Id>
    <Mode>1</Mode>
    <Enable>true</Enable>
    <Notification i:nil="true"/>
    <Notify>false</Notify>
</Recording>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'http://www.m5net.com/test/configuration/connectors')
SELECT c.value('(Id/text())[1]', 'INT') AS Id
    , c.value('(Mode/text())[1]', 'INT') AS Mode
FROM @ModeConfiguration
    CROSS APPLY config.nodes('/Recording') AS t(c);

Output

+-------+------+
|  Id   | Mode |
+-------+------+
| 16607 |    1 |
+-------+------+

Screen shot

Good link on the subject of typed XML: text() dilemma for typed XML vs. untyped XML

edited Dec 20, 2020 at 0:53

answered Dec 18, 2020 at 20:18

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user11403920 Over a year ago

I tried out this solution but it doesn't work : 'text()' is not supported on simple typed or 'w3.org/2001/XMLSchema#anyType' elements. If the other name space also need to be handled -" xmlns:i " or anything else I am missing out.

Yitzhak Khabinsky Over a year ago

@user11403920. Ii seems that your XML has a different structure. Or your column is bound to an XML Schema (XSD). That's why it is important to provide a minimal reproducible example while asking a question. I added a screen shot to the answer.

Yitzhak Khabinsky Over a year ago

@user11403920, I added a link on the subject of querying typed XML.

user11403920 · Accepted Answer · 2020-12-19 06:12:15Z

0

this worked :

select t.node.value('*:Mode[1]', 'int')
from ModeConfiguration 
    CROSS APPLY Config.nodes('*:Recording') t(node)
    WHERE  featurei=27;

answered Dec 19, 2020 at 6:12

user11403920

136 bronze badges

1 Comment

user11403920 Over a year ago

Also try to cast XML as a varchar and look for a similar value : SELECT top 1000 * FROM TABLE WHERE CAST(column as nvarchar(max)) LIKE 'check this'

Collectives™ on Stack Overflow

Extract node value from an xml from a table using SQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related