February 9, 2011 at 7:56 am
<?xml version="1.0" encoding="utf-8"?>
<a>
<b xmlns="b">
<c>germany</c>
</b>
</a>
How can you extract the word Germany using sql?
February 9, 2011 at 8:04 am
I tried this but no success
WITH XMLNAMESPACES(DEFAULT 'b')
select
tab.col.value('c[1]', 'NVARCHAR(100)') AS Country1
from
@xml.nodes('/a/b')tab(col)
February 13, 2011 at 11:43 am
I pretty much suck when it comes to handling XML. 😀
Does anyone have an answer for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 13, 2011 at 3:14 pm
Jeff, you gotta learn some XML !
p.gauci, declare the namespace explicitly and then use it to reference the nodes b and c....
;with xmlnamespaces( 'b' as ns_b )
select nd.value('(./text())[1]','varchar(20)')
from @xml.nodes('/a/ns_b:b/ns_b:c') x(nd)
Note that I gave the namespace an alias of ns_b to differentiate it from the node called "b".
Also, note that I used ./text() to extract the value - it's slightly quicker than just "."
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
February 13, 2011 at 11:16 pm
mister.magoo (2/13/2011)
Jeff, you gotta learn some XML !
Ok... how about an article on it? 😀
In the meantime, care to explain the code you wrote so an XML dummy like me can understand it? Also, is there a good book or link on the subject because, like you said, I've gotta learn it and I figure you could point me in the right direction.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 4:14 am
Jeff, I don't think another article is needed - Jacob Sebastian has already written a fantastic series of articles available here on SSC called XML Workshop - they range from the basics http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/ to more advanced techniques - ( he has done 26 of them now http://www.sqlservercentral.com/articles/XQUERY/70548/ )
I will certainly try to explain the code when I get more time - later today perhaps.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
February 14, 2011 at 7:20 am
mister.magoo (2/14/2011)
Jeff, I don't think another article is needed - Jacob Sebastian has already written a fantastic series of articles available here on SSC called XML Workshop - they range from the basics http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/ to more advanced techniques - ( he has done 26 of them now http://www.sqlservercentral.com/articles/XQUERY/70548/ )I will certainly try to explain the code when I get more time - later today perhaps.
If Jacob's articles are as good as you say, no need for you to break down your code. Thanks for the links... I'll study them tonight. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 7:46 pm
http://technet.microsoft.com/en-us/library/ms345122(SQL.90).aspx is a good white paper on XQuery that you may want to look at.
At the bottom of this white paper are links to three other decent xml white papers on XML data type, xml indexing and best practices in sql server 2005. Unfortunately, the links appear to be broken, so you might have to google the titles to get to those articles.
February 14, 2011 at 11:12 pm
nadabadan (2/14/2011)
http://technet.microsoft.com/en-us/library/ms345122(SQL.90).aspx is a good white paper on XQuery that you may want to look at.At the bottom of this white paper are links to three other decent xml white papers on XML data type, xml indexing and best practices in sql server 2005. Unfortunately, the links appear to be broken, so you might have to google the titles to get to those articles.
Thank you for the additional information. I'll check it out.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2011 at 12:42 am
Thanks mister.magoo
February 16, 2011 at 1:40 am
I was in the same boat as you regarding XML when I started using Policy Based Management and XEvents...
Bought the following book and studied it in conjunction with googling.
APress Pro SQL Server 2008 XML Written by Michael Coles.
February 16, 2011 at 6:44 am
Thanks for the book reference. I'll take a look. Michael Coles is one of the "good guys".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply