parse XML using sql
-
<?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?
-
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)
-
I pretty much suck when it comes to handling XML.
Does anyone have an answer for this?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
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);