CODEWITHSUNDEEP
sqlsql-serverxml
MarsOne
MarsOne

Reputation: 2186

Converting XML in SQL Server

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.

Upvotes: 2

Views: 206

Answers (3)

granadaCoder
granadaCoder

Reputation: 27842

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

Upvotes: 1

Luis de Haro
Luis de Haro

Reputation: 739

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.

Upvotes: 5

Orlando Herrera
Orlando Herrera

Reputation: 3531

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

Upvotes: 1

Related Questions