CODEWITHSUNDEEP
sqlsql-serverxml
jackstraw22
jackstraw22

Reputation: 641

Trying to get XML field from SQL Server

I'm getting data from a table where one of the columns is in XML format. The column is called Updated and the table is Audit. The fields look like this:

    <Fields><Field Name="DateFrom"/><Field Name = "Type 1"/><Field Name = "Type 2/></Fields>

    <Fields><Field Name = "DateFrom"/></Fields>

    <Fields><Field Name="DateFrom"/><Field Name = "Note"/><Field Name = "Type 1"/></Fields>

The XML field is part of a bigger query:

    Select id, Updated
    from Audit

The end will look something like the following, with ID being a non-XML column.

ID     Updated

123    DateFrom, Type1, Type2
323    DateFrom
455    DateFrom, Note, Type1

I've tried some things I found on-line, but I'm not doing this correctly. One method I tried was:

    Select Updated.value('/Fields/Field Name)[1]', 'nvarchar(max)') as NewUpdated from Audit.

Any ideas?

Upvotes: 0

Views: 209

Answers (1)

TriV
TriV

Reputation: 5148

You could use value, nodes, and stuff like this

   DECLARE @Audit AS TABLE
(
   Id int,
   Updated xml
)

INSERT INTO @Audit
(
   Id,
   Updated
)
VALUES
(1,N'<Fields><Field Name="DateFrom"/><Field Name = "Type 1"/><Field Name = "Type 2" /></Fields>'),
(2,N'<Fields><Field Name = "DateFrom"/></Fields>'),
(3, N'<Fields><Field Name="DateFrom"/><Field Name = "Note"/><Field Name = "Type 1"/></Fields>')    

  SELECT a.Id, ca.NewUpdated
 FROM @Audit a
   CROSS APPLY
   (
     SELECT  STUFF(
                   (SELECT ', ' + x.n.value('(./@Name)[1]', 'varchar(20)') 
                   FROM a.Updated.nodes('/Fields/Field') x(n)
                   FOR XML PATH('') 
                ),1,2,'')   AS NewUpdated  
   ) ca

Demo link: http://rextester.com/NNF94534

Upvotes: 1

Related Questions