How can the XML namespaces in an XML data type be discovered/selected?
I have a project where I have a stored procedure that takes a parameter of type XML and in responsible for extracting information from the XML into various database tables. Utilizing the
.value and
.query methods I am able to shred the XML data into the SQL 2005 database.
If I know what XML namespaces are present, utilizing WITH XMLNAMESPACES provides a means of querying XML data that has namespace defintions, but what do we do in the case where we do not know what namespaces are present?My question is if there is a way to extract all of the XML namespaces for a given XML data type parameter. I guess what I am looking for is the SQL equivalent of the XPathNavigator.GetNamespacesInScope method.
I can utilize the SQLCLR if this type of functionality is not present in SQL Server 2005, but wanted to confirm this before going to the trouble.
Martin,
Thanks, this query does indeed return all of the distinct XML namespaces utilized in the XML data type, but the result I am seeing is a single line of text with all of the XML namespaces concatenated. Is there a way to modify the quesy to return each namespace as a row in a select statement?
Here is the SQL statement I am using as a test:
Code Snippet
DECLARE @Feed XMLSET
@Feed=
'<rss version="2.0"
xmlns:blogChannel="http://backend.userland.com/blogChannelModule"
xmlns:pingback="http://madskills.com/public/xml/rss/module/pingback/"
xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/"
> <channel> <title>My Channel Title</title>
<link>http://localhost/rss</link>
<description>This is a generic RSS 2.0 syndication feed.</description> <cloud
domain="rpc.sys.com"
port="80"
path="/RPC2"
registerProcedure="myCloud.rssPleaseNotify"
protocol="xml-rpc"
/>
<copyright>Copyright 2007, Oppositionally Defiant</copyright>
<docs>http://www.rssboard.org/rss-specification</docs>
<generator>Argotic Syndication Framework</generator>
<image>
<url>http://localhost/rss/images/image.gif</url>
<title>Generic Channel Image</title>
<description>Describes the web site this feed belongs to.</description>
<link>http://localhost/rss</link>
<width>88</width>
<height>31</height>
</image>
<language>en-US</language>
<lastBuildDate>Sat, 07 Sep 2002 09:42:31 GMT</lastBuildDate>
<managingEditor>john.doe@domain.com (John Doe)</managingEditor>
<pubDate>Sat, 07 Sep 2002 00:00:01 GMT</pubDate>
<rating>PICS Rating</rating>
<textInput>
<title>The label of the Submit button in the text input area.</title>
<description>Explains the text input area.</description>
<name>The name of the text object in the text input area.</name>
<link>http://localhost/rss/cgi.script</link>
</textInput>
<ttl>60</ttl>
<webMaster>jane.doe@domain.com (Jane Doe)</webMaster> <item> <title>My Channel Item</title>
<link>http://localhost/rss/items.aspx?id=1234</link>
<description>This is a generic RSS 2.0 channel item.</description> <author>john.smith@domain.com (John Smith)</author>
<comments>http://localhost/rss/comments.aspx?id=1234</comments>
<enclosure
url="http://localhost/rss/mp3s/SomeSong.mp3"
length="12216320"
type="audio/mpeg"
/>
<guid isPermaLink="true">http://localhost/rss/2007/07/1234.aspx</guid>
<pubDate>Sun, 19 May 2002 15:21:36 GMT</pubDate>
<source url="http://www.domain.com/rss.xml">External Feed Source</source>
<category domain="mscomdomain:ContentType">SQL 2005 XML </category>
<category domain="mscomdomain:Audience">Database developers</category>
<category domain="mscomdomain:Operating Systems">Windows</category>
<category domain="mscomdomain:Subject">.NET development</category>
<pingback:server>http://localhost/rss/pingback/</pingback:server>
<pingback:target>http://localhost/rss/2007/07/1234.aspx</pingback:target>
<pingback:about>http://localhost/rss2/2007/07/1234.aspx</pingback:about> <trackback:ping>http://localhost/rss/trackback/trackback.aspx?id=1234</trackback:ping> </item> <item> <title>Another Channel Item</title>
<link>http://localhost/rss/items.aspx?id=5678</link>
<description>This is another generic RSS 2.0 channel item.</description> <author>joe.smith@domain.com (John Smith)</author>
<comments>http://localhost/rss/comments.aspx?id=5678</comments>
<enclosure
url="http://localhost/rss/mp3s/SomeOtherSong.mp3"
length="14216320"
type="audio/mpeg"
/>
<guid isPermaLink="false">http://localhost/rss/2007/07/04.aspx</guid>
<pubDate>Sun, 20 May 2002 15:21:36 GMT</pubDate>
<source url="http://www.domain.com/rss2.xml">Another Feed Source</source>
<category domain="mscomdomain:ContentType">SQLXML</category>
<category domain="mscomdomain:Audience">Application developers</category>
<category domain="mscomdomain:Operating Systems">Linux</category>
<category domain="mscomdomain:Subject">Web development</category>
</item>
<category domain="mscomdomain:ContentType">Announcement</category>
<category domain="mscomdomain:Audience">Application developers</category>
<category domain="mscomdomain:Audience">Component developers</category>
<category domain="mscomdomain:Audience">Database developers</category>
<category domain="mscomdomain:Audience">Developers (general)</category>
<category domain="mscomdomain:Audience">Game developers</category>
<category domain="mscomdomain:Audience">Web developers</category>
<category domain="mscomdomain:Operating Systems">Windows</category>
<category domain="mscomdomain:Subject">.NET development</category>
<category domain="mscomdomain:Subject">Web development</category> <skipDays>
<day>Monday</day>
<day>Tuesday</day>
<day>Wednesday</day>
<day>Thursday</day>
<day>Friday</day>
<day>Saturday</day>
<day>Sunday</day>
</skipDays> <skipHours>
<hour>0</hour>
<hour>1</hour>
<hour>2</hour>
<hour>3</hour>
<hour>4</hour>
<hour>5</hour>
<hour>6</hour>
<hour>7</hour>
<hour>8</hour>
<hour>9</hour>
<hour>10</hour>
<hour>11</hour>
<hour>12</hour>
<hour>13</hour>
<hour>14</hour>
<hour>15</hour>
<hour>16</hour>
<hour>17</hour>
<hour>18</hour>
<hour>19</hour>
<hour>20</hour>
<hour>21</hour>
<hour>22</hour>
<hour>23</hour>
</skipHours>
<blogChannel:blogRoll>http://localhost/rss/BlogRoll.opml</blogChannel:blogRoll>
<blogChannel:mySubscriptions>http://localhost/rss/mySubscriptions.opml</blogChannel:mySubscriptions>
<blogChannel:blink>http://msdn2.microsoft.com/</blogChannel:blink>
<blogChannel:changes>http://localhost/rss/changes.xml</blogChannel:changes> </channel>
</rss>
'SELECT @Feed.query('distinct-values((for $e in //* return namespace-uri($e), for $a in //@* return namespace-uri($a)))')
I needed a second variable to construct a temporary result, then the following is possible:
Code Snippet
DECLARE
@Feed XML; SET
@Feed='<rss version="2.0" xmlns:blogChannel="http://backend.userland.com/blogChannelModule" xmlns:pingback="http://madskills.com/public/xml/rss/module/pingback/" xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/" > <channel> <title>My Channel Title</title> <link>http://localhost/rss</link> <description>This is a generic RSS 2.0 syndication feed.</description> <cloud domain="rpc.sys.com" port="80" path="/RPC2" registerProcedure="myCloud.rssPleaseNotify" protocol="xml-rpc" /> <copyright>Copyright 2007, Oppositionally Defiant</copyright> <docs>http://www.rssboard.org/rss-specification</docs> <generator>Argotic Syndication Framework</generator> <image> <url>http://localhost/rss/images/image.gif</url> <title>Generic Channel Image</title> <description>Describes the web site this feed belongs to.</description> <link>http://localhost/rss</link> <width>88</width> <height>31</height> </image> <language>en-US</language> <lastBuildDate>Sat, 07 Sep 2002 09:42:31 GMT</lastBuildDate> <managingEditor>john.doe@domain.com (John Doe)</managingEditor> <pubDate>Sat, 07 Sep 2002 00:00:01 GMT</pubDate> <rating>PICS Rating</rating> <textInput> <title>The label of the Submit button in the text input area.</title> <description>Explains the text input area.</description> <name>The name of the text object in the text input area.</name> <link>http://localhost/rss/cgi.script</link> </textInput> <ttl>60</ttl> <webMaster>jane.doe@domain.com (Jane Doe)</webMaster> <item> <title>My Channel Item</title> <link>http://localhost/rss/items.aspx?id=1234</link> <description>This is a generic RSS 2.0 channel item.</description> <author>john.smith@domain.com (John Smith)</author> <comments>http://localhost/rss/comments.aspx?id=1234</comments> <enclosure url="http://localhost/rss/mp3s/SomeSong.mp3" length="12216320" type="audio/mpeg" /> <guid isPermaLink="true">http://localhost/rss/2007/07/1234.aspx</guid> <pubDate>Sun, 19 May 2002 15:21:36 GMT</pubDate> <source url="http://www.domain.com/rss.xml">External Feed Source</source> <category domain="mscomdomain:ContentType">SQL 2005 XML </category> <category domain="mscomdomain:Audience">Database developers</category> <category domain="mscomdomain:Operating Systems">Windows</category> <category domain="mscomdomain:Subject">.NET development</category> <pingback:server>http://localhost/rss/pingback/</pingback:server> <pingback:target>http://localhost/rss/2007/07/1234.aspx</pingback:target> <pingback:about>http://localhost/rss2/2007/07/1234.aspx</pingback:about> <trackback:ping>http://localhost/rss/trackback/trackback.aspx?id=1234</trackback:ping> </item> <item> <title>Another Channel Item</title> <link>http://localhost/rss/items.aspx?id=5678</link> <description>This is another generic RSS 2.0 channel item.</description> <author>joe.smith@domain.com (John Smith)</author> <comments>http://localhost/rss/comments.aspx?id=5678</comments> <enclosure url="http://localhost/rss/mp3s/SomeOtherSong.mp3" length="14216320" type="audio/mpeg" /> <guid isPermaLink="false">http://localhost/rss/2007/07/04.aspx</guid> <pubDate>Sun, 20 May 2002 15:21:36 GMT</pubDate> <source url="http://www.domain.com/rss2.xml">Another Feed Source</source> <category domain="mscomdomain:ContentType">SQLXML</category> <category domain="mscomdomain:Audience">Application developers</category> <category domain="mscomdomain:Operating Systems">Linux</category> <category domain="mscomdomain:Subject">Web development</category> </item> <category domain="mscomdomain:ContentType">Announcement</category> <category domain="mscomdomain:Audience">Application developers</category> <category domain="mscomdomain:Audience">Component developers</category> <category domain="mscomdomain:Audience">Database developers</category> <category domain="mscomdomain:Audience">Developers (general)</category> <category domain="mscomdomain:Audience">Game developers</category> <category domain="mscomdomain:Audience">Web developers</category> <category domain="mscomdomain:Operating Systems">Windows</category> <category domain="mscomdomain:Subject">.NET development</category> <category domain="mscomdomain:Subject">Web development</category> <skipDays> <day>Monday</day> <day>Tuesday</day> <day>Wednesday</day> <day>Thursday</day> <day>Friday</day> <day>Saturday</day> <day>Sunday</day> </skipDays> <skipHours> <hour>0</hour> <hour>1</hour> <hour>2</hour> <hour>3</hour> <hour>4</hour> <hour>5</hour> <hour>6</hour> <hour>7</hour> <hour>8</hour> <hour>9</hour> <hour>10</hour> <hour>11</hour> <hour>12</hour> <hour>13</hour> <hour>14</hour> <hour>15</hour> <hour>16</hour> <hour>17</hour> <hour>18</hour> <hour>19</hour> <hour>20</hour> <hour>21</hour> <hour>22</hour> <hour>23</hour> </skipHours> <blogChannel:blogRoll>http://localhost/rss/BlogRoll.opml</blogChannel:blogRoll> <blogChannel:mySubscriptions>http://localhost/rss/mySubscriptions.opml</blogChannel:mySubscriptions> <blogChannel:blink>http://msdn2.microsoft.com/</blogChannel:blink> <blogChannel:changes>http://localhost/rss/changes.xml</blogChannel:changes> </channel> </rss>'; DECLARE
@namespaces XML; SET
@namespaces = @Feed.query(' for $n in distinct-values((for $e in //* return namespace-uri($e), for $a in //@* return namespace-uri($a))) return <namespace>{$n}</namespace>
'
); SELECT
T.x.value('.', 'nvarchar(100)') FROM
@namespaces.nodes('/namespace') AS T(x); If you don't want the empty namespace URI then change the last line to
SELECT
T.x.value('.', 'nvarchar(100)') FROM
@namespaces.nodes('/namespace[. != ""]') AS T(x); 
