Parse non Xml string into Xml during Sql query

Question

Say I have this subset of data. All I need to do is have John | John | 20 as my output. The main issue I am having is that my XmlData is stored in an nvarchar(Max) field and the update to fix this, breaks an unknown amount of other applications (talking a massive scale so I cannot simply modify the table design).

Name nvarchar(23) | XmlData (nvarchar(max)                             |
John              |<Personal><name>John</name><age>20</age></Personal> |
Suzy              |<Personal><name>Suzanne</name><age>24</age></Personal> |
etc...

What I have tried so far is similar to the following, but it fails.

SELECT Name,
       [myTable].Value('(Personal[name ="name"]/value/text())[1]', 'nvarchar(100)') as 'XmlName',    
       [myTable].Value('(Personal[name ="age"]/value/text())[1]', 'nvarchar(100)') as 'XmlAge'
FROM [MyTable]

How can I achieve my goal of the following output?

Name | XmlName | XmlAge |
John | John    | 20     |
Suzy | Suzanne | 24     |
etc...

GarethD · Accepted Answer · 2015-04-09 15:48:03Z

1

First cast the field to the XML type, then use the value() method:

DECLARE @T TABLE (Name nvarchar(23), XmlData nvarchar(max));
INSERT @T VALUES ('John', '<Personal><name>John</name><age>20</age></Personal>');

SELECT Name,
       CAST(XmlData AS XML).value('(Personal/name)[1]', 'nvarchar(100)') AS 'XmlName',
       CAST(XmlData AS XML).value('(Personal/age)[1]', 'nvarchar(100)') AS 'XmlAge'
FROM @T;

answered Apr 9, 2015 at 15:48

GarethD

70k11 gold badges90 silver badges130 bronze badges

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

Collectives™ on Stack Overflow

Parse non Xml string into Xml during Sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related