SELECT XML format Data 2

[jimoo]

I have a database with a table (Authorxmldata) and a column named XML that simply has a data type of text. It contains XML data. Below is a snippet of the data.

I am trying to create a query to select the data. This is just one row but you could simple paste it in again to make multiple rows. I suspect the format is something like this, but cannot get it to work correctly. Below in the 2nd code box is a sample of the data. Any suggestions?

SELECT
xml.value('firstname[1]','varchar(30)') AS firstname,
xml.value('lastname[1]','varchar(30)') AS lastname,
xml.value('middlename[1]','varchar(30)') AS middlename
from xml.nodes('root/author') dbo.AuthorXMLData([xml])

<author><personaldata><names><name type="given"><nameprefix></nameprefix><firstname>Sammy</firstname><middlename></middlename><lastname>Sample</lastname><namesuffix></namesuffix><displayname>Aardema, Verna</displayname></name></names><birth><date>June 6, 1911-May 11, 2000</date><location>New Era, Michigan</location></birth><addresses><address addresstype=""><street>784 Via Del Sol</street><city>N. Ft. Myers</city><state>Florida</state><postalcode>33903</postalcode><country></country></address></addresses><contacts/><parents>Alfred E. Norberg, Dora Norberg</parents><spouse></spouse><children>Austin, Paula</children></personaldata><goal>"My aim is to select the best tales which anthropologists and missionaries in the field have collected, and get them into the hands of children. I believe that an appreciation of the folk lore of other cultures promotes respect and understanding. And if the stories teach a lesson children ought to learn, that is a plus. I want my stories to be fun for children to read, and exciting but not scary enough to give them nightmares."</goal><inquiries answer="no"></inquiries><education><educationentry><institution></institution><location></location><degree></degree><date>1929-1934</date><description>Michigan State College, A.B., major in composition &amp; journalism, East Lansing, Michigan, 1929-1934</description></educationentry></education><career><careerentry><organization></organization><location></location><position></position><date>1934-1935</date><description>Teacher, Pentwater, Michigan, 1934-1935.</description></careerentry><careerentry><organization></organization><location></location><position></position><date>1935-1936</date><description>Teacher, Muskegon, Michigan, 1935-1936.</description></careerentry><careerentry><organization></organization><location></location><position></position><date>1951-1973</date><description>Teacher, Mona Shores Schools, Muskegon, Michgan, 1951-1973.</description></careerentry><careerentry><organization></organization><location></location><position></position><date>1949-1971</date><description>City staff correspondent,The Muskegon Chronicle daily paper, 1949-1971</description></careerentry></career><writings><writing><description>Anansi Finds a Fool Dial l992.</description><title></title><publisher></publisher><genres/><date></date></writing><writing><description>Borreguita and the Coyote Knopf 1991.</description><title></title><publisher></publisher><genres/><date>1991</date></writing><writing><description>Pedro and the Padre Dial 1991.</description><title></title><publisher></publisher><genres/><date>1991</date></writing><writing><description>Traveling to Tondo Knopf 1991.</description><title></title><publisher></publisher><genres/><date>1991</date></writing><writing><description>Rabbit Makes a Monkey of Lion Dial 1989.</description><title></title><publisher></publisher><genres/><date>1989</date></writing><writing><description>Princess Gorilla and a New Kind of Water Dial 1988.</description><title></title><publisher></publisher><genres/><date>1988</date></writing><writing><description>Bimwili and the Zimwi Dial 1985.</description><title></title><publisher></publisher><genres/><date>1985</date></writing><writing><description>Oh, Kojo! How Could You! Dial 1984.</description><title></title><publisher></publisher><genres/><date>1984</date></writing><writing><description>The Vingananee and the Tree Toad Viking 1983.</description><title></title><publisher></publisher><genres/><date>1983</date></writing><writing><description>What's So Funny, Ketu? Viking 1982.</description><title></title><publisher></publisher><genres/><date>1982</date></writing><writing><description>Bringing the Rain to Kapiti Plain Dial 1981.</description><title></title><publisher></publisher><genres/><date>1981</date></writing><writing><description>Who's in Rabbit's House? Dial 1977.</description><title></title><publisher></publisher><genres/><date>1977</date></writing><writing><description>Why Mosquitoes Buzz in People's Ears Dial 1975.</description><title></title><publisher></publisher><genres/><date>1975</date></writing><writing><description>Behind the Back of the Mountain Dial 1973.</description><title></title><publisher></publisher><genres/><date>1973</date></writing><writing><description>Tales From the Third Ear E.P. Dutton 1969.</description><title></title><publisher></publisher><genres/><date>1969</date></writing><writing><description>More Tales From the Story Hat Coward McCann 1966.</description><title></title><publisher></publisher><genres/><date>1966</date></writing><writing><description>The Sky God Stories Coward McCann 1960.</description><title></title><publisher></publisher><genres/><date>1960</date></writing><writing><description>Otwe Coward McCann 1960.</description><title></title><publisher></publisher><genres/><date>1960</date></writing><writing><description>The Na of Wa Coward McCann 1960.</description><title></title><publisher></publisher><genres/><date>1960</date></writing><writing><description>Tales From the Story Hat Coward McCann 1960.</description><title></title><publisher></publisher><genres/><date>1960</date></writing><writing><description>Misoso: Once Upon a Time Tales of Africa Knopf 1994.</description><title></title><publisher></publisher><genres/><date>1994</date></writing><writing><description>Sebgugugu the Glutton Eerdmans.</description><title></title><publisher></publisher><genres/><date></date></writing><writing><description>A Bookworm Who Hatched Owen 1993.</description><title></title><publisher></publisher><genres/><date>1993</date></writing><writing><description>This for That Dial.</description><title></title><publisher></publisher><genres/><date></date></writing></writings><mentions><mention><date></date><description>Michigan Authors, 2nd ed.</description></mention><mention><date></date><description>Something About the Author Autobiography Series, Vol.8.</description></mention><mention><date></date><description>Borreguita and the Coyote will be a Reading Rainbow Selection.</description></mention><mention><date>1992</date><description>Traveling to Tondo, ALA Notable Book, 1992.</description></mention><mention><date>1975</date><description>Why Mosquitoes Buzz in People's Ears Caldecott winner, 1975</description></mention></mentions><skills/></author>

Jim

 

[gmmastros]

Try this:

Select Blah.Data.value('firstname[1]','varchar(30)') As FirstName,
       Blah.Data.value('lastname[1]','varchar(30)') As LastName,
       Blah.Data.value('middlename[1]','varchar(30)') AS middlename
From   AuthorXMLData
       Cross Apply xml.nodes('//author/personaldata/names/name') As Blah(Data)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimoo]

Thanks George.

There is a data type issue here. I wonder if I need to change the datatype from text to XML. That may be easier said than done since it can impacet other items so an alternate might be to create a temp table of some nature with a datatype of XML and bring the text data into it and then query from it. Sounds sloppy though.

Any thoughts:

Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.




Msg 9506, Level 16, State 1, Line 1
The XMLDT method 'nodes' can only be invoked on columns of type xml.

Jim

 

[gmmastros]

Ah... stinkin data types. The way I see it, if you want to store a date, you should use the date data type, if you want to store a whole number, use integer. If you want to store XML, then use the XML data type. I do understand the difficulties of changing to proper data types, and this is something you should certainly strive for.

Anyway, you can convert the text to XML easily enough. Something like this:

Select Blah.Data.value('firstname[1]','varchar(30)') As FirstName,
       Blah.Data.value('lastname[1]','varchar(30)') As LastName,
       Blah.Data.value('middlename[1]','varchar(30)') AS middlename
From   (Select Convert(XML, XML) As XML From AuthorXMLData) As A
       Cross Apply xml.nodes('//author/personaldata/names/name') As Blah(Data)

I don't know what would happen if the data in your text column contains invalid XML. I suspect you will get an error, so please be aware of that. This is the primary reason why using an XML data type is beneficial. With the XML data type, you are assured not to get invalid XML.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jimoo]

Thanks George. I wish that I could give you 2 stars. While you were repsonding I created another table and added an attribute with XML data type and then ran a sql command to insert the data:

insert into dbo.authorsxmldataII
select * from dbo.AuthorXMLData

Then your original query worked like a charm so I could have created a temp table too if necessary.

This gives me a lot to go on. I have more to do but I am pointed in the right direction. There are quite a few notes and I am working to parse them all into a relational database structure along with data.



Jim