Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Reading XML in SQL

Author  Topic 

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-21 : 15:57:38
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 below
SELECT top 1
a.[ShippingAddress].value('(/address/StreeLine1)[1]', 'NVARCHAR(MAX)') as 'First'
FROM [db].[dbo].[Order] a


quote:
Msg 258, Level 15, State 1, Line 3
Cannot call methods on ntext.



How would I use SQL to read XML. I followed a tutorial and that's how I got to what I have now

-Sergio
I use Microsoft SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-21 : 16:15:01
People store XML in relational databases when the data is unstructured - i.e., the columns are not predictable. Sometimes they store it because they perhaps didn't want to take the time and efforts to store it in the correct relational form.

Regardless, here is an example of how you would shred the XML you posted. XML is case sensitive even if your database collation is case-insensitive. So it is "Address", not "address".
CREATE TABLE #orders (ShippingAddress NTEXT);
INSERT INTO #orders
( ShippingAddress )
VALUES ( N'
<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>');


SELECT TOP 1
a.[ShippingAddress].value('(/Address/StreeLine1)[1]', 'NVARCHAR(MAX)') AS 'First'
FROM (SELECT CAST(ShippingAddress AS XML) AS xmlShippingAddress FROM #orders) s
CROSS APPLY xmlShippingAddress.nodes('/') a ( ShippingAddress )
DROP TABLE #orders;
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-21 : 17:57:26
Thanks! It works perfectly!

-Sergio
I use Microsoft SQL 2008
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-22 : 08:27:49
You are very welcome - glad to help
Go to Top of Page

SergioM
Posting Yak Master

170 Posts

Posted - 2014-08-08 : 12:57:41
James, I wanted to let you know that it's been over a year since you've helped me with this & I check it regularly. It has been extremely useful for me. Thanks!

-Sergio
I use Microsoft SQL 2008
Go to Top of Page
   

- Advertisement -