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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

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

SergioM
Posting Yak Master

170 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

3873 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

170 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

3873 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

170 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

porcrim
Starting Member

13 Posts

Posted - 04/22/2018 :  03:28:06  Show Profile  Reply with Quote
[b]<a href="http://www.tiffanyrings.com.cn/">cheap tiffany & co jewelry</a>[/b][b][url=http://www.tiffanyrings.com.cn/]tiffany jewelry[/url][/b]<strong><a href="http://www.tiffanyrings.com.cn/">tiffany jewelry outlet</a></strong><br><strong><a href="http://www.tiffanyrings.com.cn/">tiffany blue</a></strong><br><strong><a href="http://www.tiffanyrings.com.cn/">tiffany outlet</a></strong><br><br><title>Tiffany & Co 1837 Interlocking Circles Pendant [6a5e] - $66.00 : Professional tiffany outlet stores, tiffanyrings.com.cn</title><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><meta name="keywords" content="Tiffany & Co 1837 Interlocking Circles Pendant [6a5e] Tiffany & Co Bangles Tiffany & Co Bracelets Tiffany & Co Chains Tiffany & Co Charms Tiffany & Co Cuff Link Tiffany & Co Earrings Tiffany & Co Necklace Tiffany & Co Pendant Tiffany & Co Rings Tiffany & Co Sets cheap tiffany Jewelry online sales" /><meta name="description" content="Professional tiffany outlet stores Tiffany & Co 1837 Interlocking Circles Pendant [6a5e] - Tiffany Pendants are famous all over the world. They are known for the unconstrained style and unbelieveable design. They are the dreams of every woman who has unique taste in the world. Every Tiffany Pendants is special and unique, and you can never find the same one at " /><meta http-equiv="imagetoolbar" content="no" /><link rel="canonical" href="http://www.tiffanyrings.com.cn/tiffany-co-1837-interlocking-circles-pendant-6a5e-p-585.html" /><link rel="stylesheet" type="text/css" href="http://www.tiffanyrings.com.cn/includes/templates/polo/css/style_imagehover.css" /><link rel="stylesheet" type="text/css" href="http://www.tiffanyrings.com.cn/includes/templates/polo/css/stylesheet.css" /><link rel="stylesheet" type="text/css" href="http://www.tiffanyrings.com.cn/includes/templates/polo/css/stylesheet_css_buttons.css" /><link rel="stylesheet" type="text/css" media="print" href="http://www.tiffanyrings.com.cn/includes/templates/polo/css/print_stylesheet.css" /><select name="currency" onchange="this.form.submit();"> <option value="USD" selected="selected">US Dollar</option> <option value="EUR">Euro</option> <option value="GBP">GB Pound</option> <option value="CAD">Canadian Dollar</option> <option value="AUD">Australian Dollar</option> <option value="JPY">Jappen Yen</option> <option value="NOK">Norske Krone</option> <option value="SEK">Swedish Krone</option> <option value="DKK">Danish Krone</option> <option value="CNY">CNY</option></select><input type="hidden" name="main_page" value="product_info" /><input type="hidden" name="products_id" value="585" /></form></div></div><div class="leftBoxContainer" id="categories" style="width: 220px"><div class="sidebox-header-left main-sidebox-header-left"><h3 class="leftBoxHeading main-sidebox-header-right" id="categoriesHeading">Categories</h3></div><div id="categoriesContent" class="sideBoxContent"><div class="categories-top-list no-dots"><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-pendant-c-8.html"><span class="category-subs-selected">Tiffany & Co Pendant</span></a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-bracelets-c-2.html">Tiffany & Co Bracelets</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-bangles-c-1.html">Tiffany & Co Bangles</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-chains-c-3.html">Tiffany & Co Chains</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-charms-c-4.html">Tiffany & Co Charms</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-cuff-link-c-5.html">Tiffany & Co Cuff Link</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-earrings-c-6.html">Tiffany & Co Earrings</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-necklace-c-7.html">Tiffany & Co Necklace</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-rings-c-9.html">Tiffany & Co Rings</a></div><div class="categories-top-list "><a class="category-top" href="http://www.tiffanyrings.com.cn/tiffany-amp-co-sets-c-10.html">Tiffany & Co Sets</a></div></div></div><div class="leftBoxContainer" id="featured" style="width: 220px"><div class="sidebox-header-left "><h3 class="leftBoxHeading " id="featuredHeading">Featured - <a href="http://www.tiffanyrings.com.cn/featured_products.html">  [more]</a></h3></div><div class="sideBoxContent centeredContent"><a href="http://www.tiffanyrings.com.cn/tiffany-and-co-mini-open-heart-necklace-84c7-p-499.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Necklace/Tiffany-And-Co-Mini-Open-Heart-Necklace.jpg" alt="Tiffany And Co Mini Open Heart Necklace [84c7]" title=" Tiffany And Co Mini Open Heart Necklace [84c7] " width="130" height="158" /></a><a class="sidebox-products" href="http://www.tiffanyrings.com.cn/tiffany-and-co-mini-open-heart-necklace-84c7-p-499.html">Tiffany And Co Mini Open Heart Necklace [84c7]</a><div><span class="normalprice">$799.00 </span> <span class="productSpecialPrice">$68.00</span><span class="productPriceDiscount"><br />Save: 91% off</span></div></div><div class="sideBoxContent centeredContent"><a href="http://www.tiffanyrings.com.cn/tiffany-and-co-mini-heart-charming-tags-drop-necklace-7440-p-498.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Necklace/Tiffany-And-Co-Mini-Heart-Charming-Tags-Drop.jpg" alt="Tiffany And Co Mini Heart Charming Tags Drop Necklace [7440]" title=" Tiffany And Co Mini Heart Charming Tags Drop Necklace [7440] " width="130" height="130" /></a><a class="sidebox-products" href="http://www.tiffanyrings.com.cn/tiffany-and-co-mini-heart-charming-tags-drop-necklace-7440-p-498.html">Tiffany And Co Mini Heart Charming Tags Drop Necklace [7440]</a><div><span class="normalprice">$923.00 </span> <span class="productSpecialPrice">$67.00</span><span class="productPriceDiscount"><br />Save: 93% off</span></div></div><div class="sideBoxContent centeredContent"><a href="http://www.tiffanyrings.com.cn/tiffany-and-co-metro-cross-pendant-jewelry-35fb-p-497.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Necklace/Tiffany-and-Co-metro-cross-pendant-jewelry.jpg" alt="Tiffany and Co metro cross pendant jewelry [35fb]" title=" Tiffany and Co metro cross pendant jewelry [35fb] " width="130" height="120" /></a><a class="sidebox-products" href="http://www.tiffanyrings.com.cn/tiffany-and-co-metro-cross-pendant-jewelry-35fb-p-497.html">Tiffany and Co metro cross pendant jewelry [35fb]</a><div><span class="normalprice">$1,436.00 </span> <span class="productSpecialPrice">$68.00</span><span class="productPriceDiscount"><br />Save: 95% off</span></div></div></div></div></td> <td id="columnCenter" valign="top"> <div id="navBreadCrumb"> <a href="http://www.tiffanyrings.com.cn/">Home</a> ::  <a href="http://www.tiffanyrings.com.cn/tiffany-amp-co-pendant-c-8.html">Tiffany & Co Pendant</a> :: Tiffany & Co 1837 Interlocking Circles Pendant [6a5e]</div><div class="centerColumn" id="productGeneral"><form name="cart_quantity" action="http://www.tiffanyrings.com.cn/tiffany-co-1837-interlocking-circles-pendant-6a5e-p-585.html?action=add_product" method="post" enctype="multipart/form-data"><div style="float:left; width:350px;"><link rel="stylesheet" href="http://www.tiffanyrings.com.cn/style/jqzoom.css" type="text/css" media="screen" /><link rel="stylesheet" href="http://www.tiffanyrings.com.cn/style/jqzoomimages.css" type="text/css" media="screen" /><style type="text/css">.jqzoom{float:left;position:relative;padding:0px;cursor:pointer;width:301px;height:308px;}</style><div id="productMainImage" class="centeredContent back"><div class="jqzoom" > <a href="http://www.tiffanyrings.com.cn/tiffany-co-1837-interlocking-circles-pendant-6a5e-p-585.html" ><img src="http://www.tiffanyrings.com.cn/images//tiffany_new02/Tiffany-Pendant/Tiffany-Co-1837-Interlocking-Circles-Pendant.jpg" alt="Tiffany & Co 1837 Interlocking Circles Pendant [6a5e]" jqimg="images//tiffany_new02/Tiffany-Pendant/Tiffany-Co-1837-Interlocking-Circles-Pendant.jpg" id="jqzoomimg"></a></div><div style="clear:both;"></div><div id='jqzoomimages' class="smallimages"></div></div></div><div style="width:260px; float:left; margin-left:30px; margin-top:15px;" id='pb-left-column'><div style="font-weight:bold; padding-bottom:10px; font-size:14px;">Tiffany & Co 1837 Interlocking Circles Pendant [6a5e]</div><span id="productPrices" class="productGeneral"><span class="normalprice">$660.00 </span> <span class="productSpecialPrice">$66.00</span><span class="productPriceDiscount"><br />Save: 90% off</span></span> <div id="cartAdd"> Add to Cart: <input type="text" name="cart_quantity" value="1" maxlength="6" size="4" /><br /><br /><input type="hidden" name="products_id" value="585" /><input type="image" src="http://www.tiffanyrings.com.cn/includes/templates/polo/buttons/english/button_in_cart.gif" alt="Add to Cart" title=" Add to Cart " /> </div> <br class="clearBoth" /></div><br class="clearBoth" /> <div id="productDescription" class="productGeneral biggerText"><div class="tabTitles"><ul> <li> <h4 tid="t1" class="cur"><strong class=""><span>Description</span></strong></h4> </li></ul></div>Tiffany Pendants are famous all over the world. They are known for the unconstrained style and unbelieveable design. They are the dreams of every woman who has unique taste in the world. Every Tiffany Pendants is special and unique, and you can never find the same one at the whole party. Our Tiffany Co Outletare likely to satisfy your all desires. Moreover. What are you waiting for? Please have a visit to our online store. Welcome!<br>Product Features:<li>Name:<strong>Tiffany & Co 1837 Interlocking Circles Pendant</strong></li><li>Material: 925 sterling silver</li><li>Guarantee: Top Quality Guarantee,100% Satisfaction Guarantee.</li><li>Strap Dress</li><li>Manufacturer: Tiffany Outlet</li><li>Weared very sexy</li><li>It can be show you Temperament figure.</li><li>Package: All of our Tiffany Jewellery comes with Tiffany bag,a set of Tiffany pouch, a gift Tiffany box, Tiffany care silver card and Tiffany polishing cloth.</li></br><p>Brand story:<br>    Since 1837, the masterpieces of Tiffany & Co. have defined style and celebrated the world’s great love stories. </p><p>    Tiffany published its first Blue Book catalogue in 1845. This annual presentation of flawless craftsmanship and peerless design heralds the fall season with one of the most extensive and exquisite collections of couture jewelry on earth. These breathtaking masterpieces of exceedingly rare gems are eagerly anticipated by the world’s jewelry connoisseurs who flock to Tiffany to be the first to see and buy these one-of-a-kind treasures.</p><p>    Tiffany has always been the leader in exploring new materials and set the standard of purity for sterling silver and platinum in the U.S. In 2012, Tiffany’s RUBEDO? metal honored the company’s 175th anniversary. Capturing the light of dawn, its beauty truly glows on the skin.</p></div><br class="clearBoth" /><div align="center"><p style='text-align:center;'><a target="_blank" href="http://www.tiffanyrings.com.cn/images//tiffany_new02/Tiffany-Pendant/Tiffany-Co-1837-Interlocking-Circles-Pendant.jpg"> <a href="http://www.tiffanyrings.com.cn/tiffany-co-1837-interlocking-circles-pendant-6a5e-p-585.html" ><img src="http://www.tiffanyrings.com.cn/images//tiffany_new02/Tiffany-Pendant/Tiffany-Co-1837-Interlocking-Circles-Pendant.jpg" width=650px alt="/tiffany_new02/Tiffany-Pendant/Tiffany-Co-1837-Interlocking-Circles-Pendant.jpg"/></a></p></div><div class="centerBoxWrapper" id="similar_product"><h2 class="centerBoxHeading">Related Products</h2><table><tr><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.tiffanyrings.com.cn/tiffany-co-outlet-atlas-tag-sterling-pendant-2c08-p-601.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Pendant/Tiffany-Co-Outlet-Atlas-Tag-Sterling-Pendant.jpg" alt="Tiffany & Co Outlet Atlas Tag Sterling Pendant [2c08]" title=" Tiffany & Co Outlet Atlas Tag Sterling Pendant [2c08] " width="160" height="154" /></a></div><a href="http://www.tiffanyrings.com.cn/tiffany-co-outlet-atlas-tag-sterling-pendant-2c08-p-601.html">Tiffany & Co Outlet Atlas Tag Sterling Pendant [2c08]</a></td><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.tiffanyrings.com.cn/tiffany-co-classic-1837-collection-cushion-pendant-d450-p-596.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Pendant/Tiffany-Co-Classic-1837-Collection-CUSHION-Pendant.jpg" alt="Tiffany & Co Classic 1837 Collection CUSHION Pendant [d450]" title=" Tiffany & Co Classic 1837 Collection CUSHION Pendant [d450] " width="160" height="160" /></a></div><a href="http://www.tiffanyrings.com.cn/tiffany-co-classic-1837-collection-cushion-pendant-d450-p-596.html">Tiffany & Co Classic 1837 Collection CUSHION Pendant [d450]</a></td><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.tiffanyrings.com.cn/tiffany-co-1837-sign-pendant-3546-p-591.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Pendant/Tiffany-Co-1837-Sign-Pendant.jpg" alt="Tiffany & Co 1837 Sign Pendant [3546]" title=" Tiffany & Co 1837 Sign Pendant [3546] " width="160" height="155" /></a></div><a href="http://www.tiffanyrings.com.cn/tiffany-co-1837-sign-pendant-3546-p-591.html">Tiffany & Co 1837 Sign Pendant [3546]</a></td><td style="display:block;float:left;width:24.5%;"><div style="width:160px;height:200px;"><a href="http://www.tiffanyrings.com.cn/tiffany-co-outlet-frank-gehry-torque-pendant-56d1-p-630.html"><img src="http://www.tiffanyrings.com.cn/images/_small//tiffany_new02/Tiffany-Pendant/Tiffany-Co-Outlet-Frank-Gehry-Torque-Pendant.jpg" alt="Tiffany & Co Outlet Frank Gehry Torque Pendant [56d1]" title=" Tiffany & Co Outlet Frank Gehry Torque Pendant [56d1] " width="160" height="194" /></a></div><a href="http://www.tiffanyrings.com.cn/tiffany-co-outlet-frank-gehry-torque-pendant-56d1-p-630.html">Tiffany & Co Outlet Frank Gehry Torque Pendant [56d1]</a></td></table></div> <div id="productReviewLink" class="buttonRow back"><a href="http://www.tiffanyrings.com.cn/index.php?main_page=product_reviews_write&products_id=585"><img src="http://www.tiffanyrings.com.cn/includes/templates/polo/buttons/english/button_write_review.gif" alt="Write Review" title=" Write Review " width="98" height="19" /></a></div><br class="clearBoth" /></form></div></td> </tr></table></div><div class="footer-container"> <div id="footer" class="footer"> <div class="col4-set"> <div class="col-1"> <h4>THE CATEGORIES</h4> <ul class="links"> <li><a href="http://www.tiffanytimperman.com">TIFFANY JEWELRY </a></li> <li><a href="http://www.tiffanytimperman.com">TIFFANY AND CO</a></li> <li><a href="http://www.tiffanytimperman.com">TIFFANY BRACELETS</a></li> <li><a href="http://www.tiffanytimperman.com">TIFFANY RING</a></li> </ul> </div> <div class="col-2"> <h4>Information</h4> <ul class="links"> <li><a href="http://www.tiffanyrings.com.cn/index.php?main_page=Payment_Methods">Payment</a></li> <li><a href="http://www.tiffanyrings.com.cn/index.php?main_page=shippinginfo">Shipping & Returns</a></li> </ul> </div> <div class="col-3"> <h4>Customer Service</h4> <ul class="links"> <li><a href="http://www.tiffanyrings.com.cn/index.php?main_page=contact_us">Contact Us</a></li> <li><a href="http://www.tiffanyrings.com.cn/index.php?main_page=Payment_Methods">Wholesale</a></li> </ul> </div> <div class="col-4"><h4>Payment & Shipping</h4> <a href="http://www.tiffanyrings.com.cn/tiffany-co-1837-interlocking-circles-pendant-6a5e-p-585.html" ><img src="http://www.tiffanyrings.com.cn/includes/templates/polo/images/payment-shipping.png"></a></div> </div> <div class="add"> Copyright © 2013-2016 <a href="http://www.tiffanyrings.com.cn/#" target="_blank">Tiffany & Co Store Online</a>. Powered by <a href="http://www.tiffanyrings.com.cn/#" target="_blank">Tiffany & Co Store Online,Inc.</a> </div> </div> </div></div></div><strong><a href="http://www.tiffanyrings.com.cn/">cheap tiffany & co jewelry</a></strong><br><strong><a href="http://www.tiffanyrings.com.cn/">tiffany jewelry</a></strong><br>
Go to Top of Page
  Previous Topic Topic Next 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.07 seconds. Powered By: Snitz Forums 2000