Read XML Value in SQL Select Statement

Question

I'm trying to extract an XML value from a SQL Server column and put it into a SQL statement. My problem is that the documentation I've found doesn't explain how to do this if there are spaces or "" in the XML path.

I'm trying to extract the value property in the XML shown here (there is no namespace in the XML). The SQL Server column is called Settings:

<properties>
   <settings hwid="stream:0.0.0">
      <setting typeid="78622C19-58AE-40D4-8EEA-17351F4273B6">
         <name>Codec</name>
         <value>4</value>
      </setting>
   </settings>
</properties>

Answer 1

You can use OPENXML to retrieve data from xml, first create procedure like this:

CREATE PROCEDURE GetXmlValueProc
    @xml NVARCHAR(max)
AS
BEGIN
    
     SET NOCOUNT ON;

   DECLARE @hdoc INT;

    EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

    DECLARE @Result NVARCHAR(50);
    SELECT  value
    FROM
        OPENXML(@hdoc, '/properties/settings/setting', 2)
        WITH
        (
            value VARCHAR(100)
        );

    EXEC sp_xml_removedocument @hdoc;
END
GO

And call procedure in this way:

DECLARE @xml NVARCHAR(MAX)='<properties><settings hwid="stream:0.0.0"><setting typeid="78622C19-58AE-40D4-8EEA-17351F4273B6"><name>Codec</name><value>4</value></setting></settings></properties>'

EXEC dbo.GetXmlValueProc @xml

Even you can make procedure more generic and pass the xml path to get data.