December 21, 2009 at 11:09 am
XML (stored in a table column):
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications
with XML.</description>
</book>
<catalog>
t-sql:
SELECT tab.col.value('./author[1]','varchar(5000)') AS 'author',
tab.col.value('./title[1]','varchar(1000)') AS 'title',
tab.col.value('./genre[1]','varchar(5000)') AS 'genre',
tab.col.value('./price[1]','varchar(999)') AS 'price',
tab.col.value('./publish_date[1]','varchar(999)') AS 'publish_date',
tab.col.value('./description[1]','varchar(999)') AS 'description'
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes('//book') AS tab(col)
GO
Challenge:
I am unable to obtain the value of <book id="bk101">. Any suggestions are greatly appreciated.
+ive
December 21, 2009 at 12:01 pm
SELECT
tab.col.value('@id','varchar(5000)') AS 'BookID',
...
Attributes on a node are identified by the "@" character.
Suggestion:
Don't use the double slash (//) (e.g. "//book") to locate the nodes as this implies that you are searching for "book" nodes anywhere within the XML document. It is "bad" coding and impacts performance.
It is better to fully qualify the path to the nodes if you can. So in your case, the "nodes" query should be:
CROSS APPLY
xml_data.nodes('catalog/book') AS tab(col)
December 21, 2009 at 3:21 pm
Thanks for your knowledge transfer.
Just upping the ante for the XML.
<bookstore='MomnPop' submission_type="LEARN" fc:xmlns="http://www.xml.org" xmlns:fc="http://www.xml.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.xml.org https://xml.org/schema/bookstore_submission.xsd">
<header>Knowledge Transfer</header>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications
with XML.</description>
</book>
</bookstore>
SELECT tab.col.value('@id', 'varchar(5000)') as 'Book_id',
tab.col.value('./author[1]','varchar(5000)') AS 'id',
tab.col.value('./title[1]','varchar(1000)') AS 'city',
tab.col.value('./genre[1]','varchar(5000)') AS 'state',
tab.col.value('./price[1]','varchar(999)') AS 'country',
tab.col.value('./publish_date[1]','varchar(999)') AS 'firstname',
tab.col.value('./description[1]','varchar(999)') AS 'lastname'
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes('bookstore/book') AS tab(col)
GO
The above sql works if I make the <bookstore> tag just <bookstore> instead of
<bookstore='MomnPop' submission_type="LEARN" fc:xmlns="http://www.xml.org" xmlns:fc="http://www.xml.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.xml.org https://xml.org/schema/bookstore_submission.xsd">
Hints on overcomming the new xml with select above.
Thanks as always.
+ive
December 21, 2009 at 4:07 pm
The beginning of the XML (<bookstore='MomnPop' ) is not considered as a well formed xml.
For two reasons:
1) you cannot assign a value to an element if you assign attributes to the same element. So, instead of bookstore='MomnPop' it should be bookstore attrib="MomnPop"
2) as shown already in the example above, to qualify a text value, use " instead of '.
I can't see a way to query that file as xml directly. One way would be to import is a text (or varchar(max)), replace bookstore= with bookstore attrib= and find a way to get proper text qualifier.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply