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-05 14:58:16Z

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 5, 2014 at 14:58

answered Aug 5, 2014 at 14:26

Luis de Haro

7391 gold badge7 silver badges29 bronze badges

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

3 Comments

Gouri Shankar Aechoor Over a year ago

you beet me to it. :)

granadaCoder Over a year ago

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 Over a year ago

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).

Community · Accepted Answer · 2017-05-23 12:20:50Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Aug 5, 2014 at 14:35

Orlando Herrera

3,5491 gold badge36 silver badges45 bronze badges

Comments

granadaCoder · Accepted Answer · 2014-08-05 15:04:14Z

1

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

edited Aug 5, 2014 at 15:04

answered Aug 5, 2014 at 14:30

granadaCoder

28.2k11 gold badges124 silver badges166 bronze badges

Collectives™ on Stack Overflow

Converting XML in SQL Server

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related