CODEWITHSUNDEEP
sql-serverxml
Nancy Ghazal
Nancy Ghazal

Reputation: 5

select the value of the first <field> in xml column in sql

I have a table in sql database contains an xml column , I just want to export all the fields of the table but the xml column I just want the value of the first which named "CentrisNo", attached you may find sample of the table. I do really appreciate your help.xml field

Upvotes: 0

Views: 1282

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your XML picture is almost useless. Please poste sample data copy'n'pasteable!

Are you looking for something like this:

DECLARE @dummy TABLE(WebSit VARCHAR(100),Note VARCHAR(100),XMLData XML);
INSERT INTO @dummy VALUES
 ('www.blah.com','Some blah','<Fields><Field Name="my name is blah" Value="blah"/><Field Name="second" Value="2."/></Fields> ')
,('www.plop.com','Some plop','<Fields><Field Name="my name is plop" Value="plop"/><Field Name="second" Value="2."/></Fields> ')

SELECT * 
      ,d.XMLData.value('(/Fields/Field/@Name)[1]','nvarchar(max)') AS FirstFieldName
      ,d.XMLData.value('(/Fields/Field/@Value)[1]','nvarchar(max)') AS FirstFieldValue
FROM @dummy AS d

The result

Note        FirstFieldName   FirstFieldValue
Some blah   my name is blah  blah
Some plop   my name is plop  plop

UPDATE

From your comments I take, that the column is not of type XML. just wrap the XMLData as CAST(XMLData AS XML).value(...). This should solve your issue...

Upvotes: 0

manderson
manderson

Reputation: 891

select CAST(XMLData AS XML).value('(/Fields/Field/@Name)[1]', 'nvarchar(max)')
from yourTable

Upvotes: 2

BeanFrog
BeanFrog

Reputation: 2315

If your column XMLData is some sort of string, you will need to convert it first. Also, this answer allows for when CentrisNo is not the first field in the list.

(replace @xmldata with your table name).

;with xmltable as (select Website, Note, cast(XMLData as xml) as XmlData from @xmltable)
select Website, Note, Value as CentrisNo from 
(   select Website, Note
          ,C.value('@Name', 'nvarchar(100)') as Name
          ,C.value('@Value', 'nvarchar(100)') as Value
    from xmltable cross apply
         xmltable.XMLData.nodes('Fields/Field') as X(C)
) as base
where base.Name = 'CentrisNo'

Upvotes: 0

Related Questions