Converting XML in SQL Server

Question

I have a Database Table where i have some data in XML datatype.

Following is an example of some data in that column.

<locale en-US="Test &amp; Data" />

Is there any way to extract only the words "Test & Data" in SQL server. Is there any built in function.

Luis de Haro · Accepted Answer · 2014-08-05T14:58:16.960

5

Try something like this.

If you have a XML variable:

declare @xml XML = '<locale en-US="Test &amp; Data" />';

select 
  data.node.value('@en-US', 'varchar(11)') my_column
from @xml.nodes('locale') data(node);

In your case, for a table's column (sorry for not given this example first):

create table dbo.example_xml
(
    my_column XML not null
);
go

insert into dbo.example_xml
values('<locale en-US="Test &amp; Data" />');
go

select
  my_column.value('(/locale/@en-US)[1]', 'varchar(11)') [en-US]
from dbo.example_xml;
go

Hope it helps.

edited Aug 05 '14 at 14:58

answered Aug 05 '14 at 14:26

Luis de Haro

711
1
7
28

you beet me to it. :) – Gouri Shankar Aechoor Aug 05 '14 at 14:32
He mentioned he has a Table with a Column of Xml(datatype). This is good for "stand-alone" xml...but does not address the it being in a column. – granadaCoder Aug 05 '14 at 14:33
1

My hint to you (and your correction) now makes my answer less likely to be accepted. 8) Such is life on the SOF highway. (haha). – granadaCoder Aug 05 '14 at 15:07

granadaCoder · Answer 2 · 2014-08-05T15:04:14.017

IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end


CREATE TABLE #Holder
(ID INT , MyXml xml )


/* simulate your insert */
INSERT INTO #HOLDER (ID , MyXml) 
select 1 , '<locale en-US="Test &amp; Data" />' 
union all select 2 , '<locale en-US="Barney &amp; Friends" />'

/* with other values in the table */

SELECT
    holderAlias.ID , 
    pre.value('(@en-US)[1]', 'Varchar(50)') AS 'ItemID'
FROM
    #Holder holderAlias CROSS APPLY
      MyXml.nodes('/locale') AS MyAlias(pre)

/* OR */

SELECT
    [MyShreddedValue] = holderAlias.MyXml.value('(/locale/@en-US)[1]', 'varchar(50)')
FROM
    #Holder holderAlias


IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
    drop table #Holder
end

score 1 · Answer 3 · edited May 23 '17 at 12:20

1

Please take a look in this example called: How to read XML column in SQL Server 2008? (here in stack overflow)

This is a very simple example:

SELECT 
Configuracion.value('(/YourTag/Values)[1]', 'VARCHAR(255)')as columnName, 
FROM yourTable

edited May 23 '17 at 12:20

Community

1
1

answered Aug 05 '14 at 14:35

Orlando Herrera

3,481
1
34
44

Converting XML in SQL Server

3 Answers3