Extract values from SQL Server XML column

Question

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)

Separate out xml columns value in a XML parametar and apply select like below Select t.item.query('./v').value(',','NVARCHAR(MAX)') from @XML.nodes('/v') as t(item) — Syed Wahhab
– Syed Wahhab, Commented Nov 20, 2019 at 5:08

Yitzhak Khabinsky · Accepted Answer · 2019-11-20 06:42:06Z

6

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'           |
+----+------------------+

edited Nov 20, 2019 at 6:42

answered Nov 20, 2019 at 5:50

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

Great, +1 from my side. Just one tiny hint: If there is no <v> element, this will return as NULL (due to the +-concatenation). Might be better to use CONCAT(), but this depends on the the OP's needs and might be perfectly okay here...

Gnyasha · Accepted Answer · 2019-11-20 12:45:28Z

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

Collectives™ on Stack Overflow

Extract values from SQL Server XML column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related