sql-server - SQL Server - XML 解析返回 NULL
问题描述
努力解析xml
第三方生成的数据。检查结构是否没有问题XML
,并且已经解析了几十个类似的结构XMLs
没有任何问题。
XML
得到了这个结构:
<rss xmlns:media="https://...." version="2.0">
<channel>
<title>Title</title>
<link>Link</link>
<description>description</description>
<pubDate>01.01.1900</pubDate>
<item>
<guid isPermaLink="false">aaaa</guid>
<title>Title</title>
<description>description</description>
<pubDate>pubDate</pubDate>
<category>Category</category>
<enclosure url="https:..." length="72" type="video/mp4" />
<media:content type="video/mp4" url="https://..." duration="72" lang="en">
<media:category>AAAA</media:category>
<media:tags>BBB</media:tags>
<media:keywords>CCCC</media:keywords>
<media:thumbnail url="https://...." width="1280" height="720" />
<media:credit role="producer" scheme="urn:ebu">DDDD</media:credit>
</media:content>
</item>
我m unable to parse anything which is under
media:content . Where full path to it should be
/rss/channel/item/media:content/media:thumbnail`,但显然不是。
大概是遗漏了什么不为人知的东西。
如果有人能就此向我提出建议,我将不胜感激,并祝大家新年快乐!!!:)
解决方案
正如我在评论中提到的,您需要在 SQL 中声明您的命名空间:
DECLARE @XML xml = '<rss xmlns:media="https://...." version="2.0">
<channel>
<title>Title</title>
<link>Link</link>
<description>description</description>
<pubDate>01.01.1900</pubDate>
<item>
<guid isPermaLink="false">aaaa</guid>
<title>Title</title>
<description>description</description>
<pubDate>pubDate</pubDate>
<category>Category</category>
<enclosure url="https:..." length="72" type="video/mp4" />
<media:content type="video/mp4" url="https://..." duration="72" lang="en">
<media:category>AAAA</media:category>
<media:tags>BBB</media:tags>
<media:keywords>CCCC</media:keywords>
<media:thumbnail url="https://...." width="1280" height="720" />
<media:credit role="producer" scheme="urn:ebu">DDDD</media:credit>
</media:content>
</item>
</channel>' + --Assumed exists
'</rss>'; --Assumed exists
WITH XMLNAMESPACES ('https://....' as media) --'https://....' would be replaced with your real namespace
SELECT c.i.value('media:content[1]/media:thumbnail[1]/@url', 'nvarchar(max)') as [Thubmnail]
FROM @xml.nodes('/rss/channel/item') AS c(i);