{"id":362,"date":"2011-11-30T19:36:10","date_gmt":"2011-11-30T18:36:10","guid":{"rendered":"http:\/\/floris.briolas.nl\/floris\/?p=362"},"modified":"2011-12-08T13:44:11","modified_gmt":"2011-12-08T12:44:11","slug":"sql-server-xml-cheetsheet","status":"publish","type":"post","link":"https:\/\/floris.briolas.nl\/floris\/2011\/11\/sql-server-xml-cheetsheet\/","title":{"rendered":"Sql Server XML Cheetsheet"},"content":{"rendered":"

Cheet sheet XML Features SQL Server<\/h1>\n

Pre-requirement: Knowledge of XPath<\/p>\n

All the samples are based on this XML:<\/p>\n\n\n\n
\n
<MyBooks>\r\n\t<ListOwner>John Smith<\/ListOwner>\r\n\t<Book>\r\n\t\t<title published=\"1885\">The Adventures of Huckleberry Finn<\/title>\r\n\t\t<author realName=\"Samuel Langhorne Clemens\">Mark Twain<\/author>\r\n\t<\/Book>\r\n\t<Book>\r\n\t\t<title published=\"1894\">Tom Sawyer Abroad<\/title>\r\n\t\t<author realName=\"Samuel Langhorne Clemens\">Mark Twain<\/author>\r\n\t<\/Book>\r\n<\/MyBooks><\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Extract Values
\nselect @xml.value(‘( \/MyBooks\/ListOwner )[1]’, ‘varchar(20)’);<\/p>\n
Possible error:<\/p>\n\n\n\n\n
error<\/th>\nfix<\/th>\n<\/tr>\n
‘value()’ requires a singleton (or empty sequence), found operand of type<\/td>\nplace the XPATH query between ‘()[1]’<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Check if exists<\/p>\n
select @xml.exist(‘ \/\/Book[author\/@realName=”Samuel Langhorne Clemens”] ‘)
\nselect @xml.exist(‘ \/\/Book[contains((author\/@realName)[1],”Langhorne”)] ‘)<\/p>\n
Extract Node<\/p>\n
SELECT @xml.query(‘\/MyBooks\/Book’) –Results all book nodes.SELECT @xml.query(‘\/MyBooks\/Book[title=”The Adventures of Huckleberry Finn”]’)SELECT @xml.query(‘\/MyBooks\/Book[title\/@published<1890]’)<\/p>\n
Select nodes<\/p>\n
This will allow you to run SQL queries on XML.<\/p>\n
select t.c.value(‘(title)[1]’,’varchar(20)’) from @xml.nodes(‘ \/\/Book ‘) as t(c)<\/strong><\/p>\n
Modify Xml<\/p>\n
Namespaces:<\/h2>\n
When there are namespaces in you XML you will have to do additional work.<\/p>\n","protected":false},"excerpt":{"rendered":"
Cheet sheet XML Features SQL Server Pre-requirement: Knowledge of XPath All the samples are based on this XML: <MyBooks> <ListOwner>John Smith<\/ListOwner> <Book> <title published=”1885″>The Adventures of Huckleberry Finn<\/title> <author realName=”Samuel Langhorne Clemens”>Mark Twain<\/author> <\/Book> <Book> <title published=”1894″>Tom Sawyer Abroad<\/title> <author realName=”Samuel Langhorne Clemens”>Mark Twain<\/author> <\/Book> <\/MyBooks> Extract Values select @xml.value(‘( \/MyBooks\/ListOwner )[1]’, ‘varchar(20)’); Possible error: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_newsletter_tier_id":0,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[3],"tags":[16,17],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p61yPs-5Q","_links":{"self":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts\/362"}],"collection":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/comments?post=362"}],"version-history":[{"count":6,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts\/362\/revisions"}],"predecessor-version":[{"id":369,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/posts\/362\/revisions\/369"}],"wp:attachment":[{"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/media?parent=362"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/categories?post=362"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/floris.briolas.nl\/floris\/wp-json\/wp\/v2\/tags?post=362"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}