I have no idea why anyone would store an XML block in SQL, but now I need to read it.XML looks like this: <?xml version="1.0" encoding="utf-16"?> <Address> <ID>-1</ID> <FirstName>Joe</FirstName> <MiddleInitial /> <LastName>FakeName</LastName> <CompanyName /> <StreeLine1>501 East 34th street</StreeLine1> <StreetLine2 /> <City>New York</City> <StateName>NY</StateName> <StateCode>29</StateCode> <PostalCode>10001</PostalCode> <CountryName>United States</CountryName> <CountryCode>en-US</CountryCode> <PhoneNumber /> <FaxNumber /> <WebSiteUrl /> <AddressSource>17</AddressSource> <AddressStatus>0</AddressStatus> <AddressClassification>0</AddressClassification> </Address>
I tried this command, but it gave me the error belowSELECT top 1a.[ShippingAddress].value('(/address/StreeLine1)[1]', 'NVARCHAR(MAX)') as 'First'FROM [db].[dbo].[Order] a
quote:
Msg 258, Level 15, State 1, Line 3Cannot call methods on ntext.