CODEWITHSUNDEEP
sql-serverxmlt-sqlparsingselect
Volearix
Volearix

Reputation: 1593

Parse non Xml string into Xml during Sql query

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...

Upvotes: 0

Views: 55

Answers (1)

GarethD
GarethD

Reputation: 69759

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;

Upvotes: 1

Related Questions