Trying to get XML field from SQL Server

Question

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?

If you need to get each field, you should use a cross apply with XML nodes. If you only need the first one, you can adjust your current query as such: (Fields/Field/@Name)[1]. A cross apply could work like this: SELECT NewUpdated = A.B.value('@Name', 'nvarchar(max)') FROM Audit CROSS APPLY Audit.Updated.nodes('Fields/Field') AS A(B); — ZLK
– ZLK, Commented Jun 13, 2017 at 0:09
I need to get all the fields. I'm trying the cross apply but I'm getting an error: "Audit.updated.nodes' is not a recognized function name. I'm trying this: select newupdated = A.B.value('@name', 'nvarchar(max)' from Audit Cross Apply Audit.updated.nodes('Fields/Field') as A(B); — jackstraw22
– jackstraw22, Commented Jun 13, 2017 at 1:19
Uh, what version of SQL Server are you using? Outside of the obvious typo in what you pasted (no closed bracket on .value(...)), there's no reason I can think of for why that wouldn't work unless you're using, say, SQL Server 2005. — ZLK
– ZLK, Commented Jun 13, 2017 at 1:32
I'm using SQL Server 2016. I do have the closed paren on value in my query. I'm literally using what you suggested. There is a red line under the A.B.value with the message "Cannot find either column "A" or the user defined function or aggregate "A.B.value", or the name is ambiguous." — jackstraw22
– jackstraw22, Commented Jun 13, 2017 at 1:36
I've tried this: select t.c.value('Fields[1]', 'varchar(100)') mdata from Audit CROSS APPLY updated.nodes('/Field Name') as t(c). This give me an error "Syntax error near 'Field.'" If I replace 'Field Name' with 'Fields' it runs but just returns NULL for all rows. — jackstraw22
– jackstraw22, Commented Jun 13, 2017 at 1:53

TriV · Accepted Answer · 2017-06-13 07:11:19Z

1

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

edited Jun 13, 2017 at 7:11

answered Jun 13, 2017 at 3:20

TriV

5,1582 gold badges12 silver badges18 bronze badges

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

5 Comments

jackstraw22 Over a year ago

This looks interesting. Would this work if the number of field names varied by record? Some records will have "DateFrom" and "Type1" and "Notes." Others may only have "DateFrom", etc. It looks as though you're hardcoding the Values. Values would need to be populated dynamically with the values for the row. I also need all the values that appear in one row to also show up in one row in the code: DateFrom, Type1, Type2 would need to appear on one row if those are the fields for that particular record.

TriV Over a year ago

I did not harcode any thing. If your 'Field' element has 'Name' attribute then it will work. Could you edit your question, provide your expected result?

jackstraw22 Over a year ago

I've updated the question to be more specific.I was just thinking that you manually entered the values into the VALUES part. The XML in the question is exact. It uses <Fields><Field Name ="DateFrom"/>...

TriV Over a year ago

You miss alias for Audit and ( before select stuff. Just change to

Select ca.newupdated  FROM Audit a CROSS APPLY ( 			Select stuff( (Select ', ' + x.n.value('(./@Name)[1]', 'varchar(50)')  			FROM a.Updated.Nodes('/Fields/Field') x(n) for xml path(' ')),1,2, ' ') as newupdated 			) ca

jackstraw22 Over a year ago

This does work, but the report runs a lot slower now. I'm assuming because it has to extract all the xml code. Is there a way to speed it up?

Collectives™ on Stack Overflow

Trying to get XML field from SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related