SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Reading XML in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

152 Posts

Posted - 08/21/2013 :  15:57:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/21/2013 :  16:15:01  Show Profile  Reply with Quote
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

152 Posts

Posted - 08/21/2013 :  17:57:26  Show Profile  Reply with Quote
Thanks! It works perfectly!

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

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 08/22/2013 :  08:27:49  Show Profile  Reply with Quote
You are very welcome - glad to help
Go to Top of Page

SergioM
Posting Yak Master

152 Posts

Posted - 08/08/2014 :  12:57:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000