parse XML using sql

Question

Post reply

parse XML using sql

p.gauci

SSC Enthusiast

Points: 166
More actions
February 9, 2011 at 7:56 am

#96178

<?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?
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]

p.gauci SSC Enthusiast Points: 166 More actions · Answer 1

p.gauci

SSC Enthusiast

Points: 166

February 9, 2011 at 8:04 am

#1284491

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)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

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);

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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)
Intro to Tally Tables and Functions

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

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://qa.sqlservercentral.com/articles/SS2K5+-+XML/3022/ to more advanced techniques - ( he has done 26 of them now http://qa.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);

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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://qa.sqlservercentral.com/articles/SS2K5+-+XML/3022/ to more advanced techniques - ( he has done 26 of them now http://qa.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

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)
Intro to Tally Tables and Functions

nadabadan SSCommitted Points: 1503 More actions · Answer 7

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

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

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)
Intro to Tally Tables and Functions

p.gauci SSC Enthusiast Points: 166 More actions · Answer 9

p.gauci

SSC Enthusiast

Points: 166

February 16, 2011 at 12:42 am

#1287475

Thanks mister.magoo

SQLArcher SSC Eights! Points: 950 More actions · Answer 10

SQLArcher

SSC Eights!

Points: 950

February 16, 2011 at 1:40 am

#1287483

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.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Thanks for the book reference. I'll take a look. Michael Coles is one of the "good guys".

--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)
Intro to Tally Tables and Functions