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>

While asking a question, you need to provide a minimal reproducible example. Please refer to the following link: stackoverflow.com/help/minimal-reproducible-example Please provide the following: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;) — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Feb 21, 2021 at 6:50
Sorry Yirzhak, yes it is. Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor) — ValiantSirDK
– ValiantSirDK, Commented Feb 21, 2021 at 6:51

sa-es-ir · Accepted Answer · 2021-02-21 08:39:29Z

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.

answered Feb 21, 2021 at 8:39

sa-es-ir

5,1902 gold badges20 silver badges38 bronze badges

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

1 Comment

ValiantSirDK Over a year ago

Thanks Saeed, I was able to get the data required with this procedure.

Charlieface · Accepted Answer · 2021-02-21 06:57:50Z

0

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   and a double-quote as "

edited Feb 21, 2021 at 6:57

answered Feb 21, 2021 at 6:39

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

2 Comments

ValiantSirDK Over a year ago

Hi Charlieface. I removed some of the XML as it's a big file. There are other setting sections which are all called the same thing except the hwid and typeid parts. Is there a way I can include that in the XML select?

Charlieface Over a year ago

Have edited. If you want to break out a single XML blob into separate rows, please be clearer. Include more of the XML (not the whole thing, just a sample structure) and what info you want from it

Collectives™ on Stack Overflow

Read XML Value in SQL Select Statement

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related