CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
ValiantSirDK
ValiantSirDK

Reputation: 75

Read XML Value in SQL Select Statement

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>

Upvotes: 1

Views: 220

Answers (2)

sa-es-ir
sa-es-ir

Reputation: 5102

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.

Upvotes: 1

Charlieface
Charlieface

Reputation: 72415

I don't see any spaces in your XML. If you mean the various attributes, such as hwid, those are parsed separately from the node names. You can select those by prefacing with @.

I assume the type of the value node is int, if not you can change it below:

SELECT
    t.Settings.value('(/properties/settings/setting/value)[1]', 'int'),
    t.Settings.value('(/properties/settings/setting/@typeid)[1]', 'uniqueidentifier'),
    t.Settings.value('(/properties/settings/@hwid)[1]', 'nvarchar(max)')
FROM myTable t

For reference, if you ever did have a node with a space in it: it would be encoded &#32; and a double-quote as &#34;

Upvotes: 0

Related Questions