CODEWITHSUNDEEP
sqlxmlt-sql
user11403920
user11403920

Reputation: 13

Extract node value from an xml from a table using SQL

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

Upvotes: 0

Views: 438

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

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 enter image description here

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

Upvotes: 2

user11403920
user11403920

Reputation: 13

this worked :

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

Upvotes: 0

Related Questions