CODEWITHSUNDEEP
sql-serverxml
Richard
Richard

Reputation: 53

Extract values from SQL Server XML column

I have a table which stores below messages in XML format. It stores different datatypes in <v> tags.

Table: #xmlmsg

fvalue
-----------------------------------
<vs><v>Sam</v></vs>
<vs><v>David</v><v>Nathan</v></vs>
<vs><v>25.5<v></vs>

I want to extract the value as string

'sam'
'David','Nathan'
'25.5'

I tried the following SQL but it returns null. I'm not sure what I'm missing

select x.rs.value('@fvalue', 'varchar(200)') as Val
from #xmlmsg
cross apply #xmlmsg.fvalue.nodes('//vs/v') As x(rs)

Upvotes: 2

Views: 391

Answers (2)

Gnyasha
Gnyasha

Reputation: 684

You may try the below query if you are dynamically looking up with Id 

DECLARE @id as int =1
DECLARE @xmlMessage XML = (SELECT fvalue from xmlmsg where Id=@id)

SELECT 
    ColumnName = A.value('local-name(.)', 'varchar(max)'),
    ColumnValue = A.value('(.)[1]', 'varchar(max)')
FROM 
    @xmlMessage.nodes('vs/*') AS B(A)

Here are the scripts to generate the sample table and data

USE [MyDatabase]
GO
/****** Object:  Table [dbo].[xmlmsg]    Script Date: 20-Nov-19 2:41:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[xmlmsg](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [fvalue] [xml] NULL,
 CONSTRAINT [PK_XmlMessages] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[xmlmsg] ON 
GO
INSERT [dbo].[xmlmsg] ([Id], [fvalue]) VALUES (1, N'<vs><v>Sam</v></vs><vs><v>David</v><v>Nathan</v></vs><vs><v>25.5</v></vs>')
GO
SET IDENTITY_INSERT [dbo].[xmlmsg] OFF
GO

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

Please try the following SQL with XQuery and FLWOR expression to get comma separated list of values.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, fvalue XML);
INSERT INTO @tbl (fvalue) 
VALUES ('<vs>
        <v>Sam</v>
    </vs>
    <vs>
        <v>David</v>
        <v>Nathan</v>
    </vs>
    <vs>
        <v>25.5</v>
    </vs>');
-- DDL and sample data population, end

SELECT ID
    , '''' + STUFF(c.query('for $s in v/text()
                           return <x>{concat("'',''",$s)}</x>')
                   .value('.','VARCHAR(MAX)'),1,3,'') + '''' AS [Val]
FROM @tbl as tbl
    CROSS APPLY tbl.fvalue.nodes('/vs') AS t(c);

Output

+----+------------------+
| ID |       Val        |
+----+------------------+
|  1 | 'Sam'            |
|  1 | 'David','Nathan' |
|  1 | '25.5'           |
+----+------------------+

Upvotes: 6

Related Questions