{"id":217,"date":"2011-03-12T10:47:15","date_gmt":"2011-03-12T02:47:15","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=217"},"modified":"2013-12-30T13:59:19","modified_gmt":"2013-12-30T05:59:19","slug":"xml%e6%96%87%e4%bb%b6%e5%af%bc%e5%85%a5sql-server","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=217","title":{"rendered":"XML\u6587\u4ef6\u5bfc\u5165SQL Server"},"content":{"rendered":"

\u4f7f\u7528<\/span>\u6709\u9053\u8bcd\u5178<\/span>\u7684\u7ae5\u978b\u4eec\u6ce8\u610f\u5566<\/span>.<\/span><\/span><\/span><\/p>\n

\u60f3\u95ee\u4e00\u4e0b\u5927\u5bb6\u4f7f\u7528\u8bcd\u5178\u7684\u751f\u8bcd\u672c\u529f\u80fd\u65f6\u6709\u6ca1\u6709\u4ee5\u4e0b\u7684\u56f0\u6270<\/span>:<\/span><\/span><\/span><\/p>\n

1.<\/span>\u5b57\u4f53\u5f88\u4e11<\/span>;<\/span><\/span><\/span><\/p>\n

2.<\/span>\u6253\u5370\u51fa\u6765\u683c\u5f0f\u5f88\u4e71<\/span>,<\/span>\u8bbe\u7f6e\u6210\u81ea\u5df1\u60f3\u8981\u7684\u683c\u5f0f\u592a\u9ebb\u70e6<\/span>;<\/span><\/span><\/span><\/p>\n

3.<\/span>\u4e0d\u80fd\u590d\u5236\u51fa\u6765<\/span>,<\/span>\u6bcf\u6b21\u67e5\u770b\u90fd\u8981\u5728\u7535\u8111\u4e0a<\/span>;<\/span><\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

\u90a3\u4e48\u8bf7\u770b\u4e0b\u9762\u51e0\u6b65<\/span>,<\/span>\u8f7b\u677e\u641e\u5b9a<\/span>.<\/span><\/span><\/span><\/p>\n

1.<\/span>\u9009\u62e9\u751f\u8bcd\u672c<\/span><\/span><\/span><\/p>\n

\"\"<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

2.<\/span>\u9009\u62e9<\/span>“<\/span>\u5bfc\u51fa<\/span>“<\/span>\u9009\u9879<\/span><\/span><\/span><\/p>\n

\"\"<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

3.<\/span>\u5bfc\u51fa\u5230\u67d0\u4e00\u8def\u5f84\u4e0b<\/span>,<\/span>\u5982<\/span>:”D:\/3.xml”<\/span><\/span><\/span><\/p>\n

\"\"<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

4.<\/span>\u6253\u5f00<\/span>SQL Server,<\/span>\u5e76\u6267\u884c\u4ee5\u4e0b\u811a\u672c<\/span><\/span><\/span><\/p>\n

— \u521b\u5efa\u4fdd\u5b58\u4f46\u6210\u672c\u7684\u6570\u636e\u5e93<\/span><\/span><\/p>\n

CREATE DATABASE YouDaoWordList<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

— \u521b\u5efa\u4fdd\u5b58\u5355\u8bcd\u672c\u7684\u8868<\/span><\/span><\/p>\n

USE YouDaoWordList<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

CREATE TABLE WordList<\/span><\/span><\/p>\n

(word\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR(50),<\/span><\/span><\/p>\n

\u00a0trans\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR(50) ,<\/span><\/span><\/p>\n

\u00a0phonetic\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NVARCHAR(50),<\/span><\/span><\/p>\n

\u00a0tags\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VARCHAR(50)<\/span><\/span><\/p>\n

)<\/span><\/span><\/p>\n

— \u5c06XML\u6587\u4ef6\u5bfc\u5165\u5230SQL Server\u4e2d<\/span><\/span><\/p>\n

DECLARE @h\u00a0\u00a0int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— \u5b9a\u4e49xml\u53e5\u67c4<\/span><\/span><\/p>\n

DECLARE @doc\u00a0\u00a0xml\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— \u5b9a\u4e49xml\u5bf9\u8c61<\/span><\/span><\/p>\n

SELECT @doc = BulkColumn FROM OPENROWSET (BULK ‘C:\/3.xml’, SINGLE_BLOB) AS xmlData\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— \u83b7\u5f97xml\u5bf9\u8c61<\/span><\/span><\/p>\n

EXECUTE sp_xml_preparedocument @h OUTPUT, @doc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— \u83b7\u5f97xml\u53e5\u67c4<\/span><\/span><\/p>\n

— \u63d2\u5165\u8868\u4e2d<\/span><\/span><\/p>\n

INSERT INTO WordList SELECT * FROM OPENXML(@h, ‘\/wordbook\/item’, 2) with WordList<\/span><\/span><\/p>\n

EXECUTE sp_xml_removedocument @h\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0— \u5220\u9664xml\u53e5\u67c4<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

5.<\/span>\u67e5\u8be2\u7ed3\u679c<\/span>,<\/span>\u5982\u56fe\u6240\u793a<\/span><\/span><\/span><\/p>\n

— \u67e5\u8be2\u7ed3\u679c<\/span><\/span><\/p>\n

SELECT word, phonetic, trans<\/span><\/span><\/p>\n

FROM WordList<\/span><\/span><\/p>\n

ORDER BY word<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\"\"<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

6.<\/span>\u73b0\u5728\u5c31\u53ef\u4ee5\u590d\u5236\u51fa\u6765\u5230<\/span>Word<\/span>\u6216\u8005<\/span>Excel,<\/span>\u7f16\u8f91\u6210\u81ea\u5df1\u559c\u6b22\u7684\u683c\u5f0f<\/span>,<\/span>\u6253\u5370\u51fa\u6765\u968f\u65f6\u7ffb\u9605\u4e86<\/span>.<\/span><\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

\u4f7f\u7528\u6709\u9053\u8bcd\u5178\u7684\u7ae5\u978b\u4eec\u6ce8\u610f\u5566. \u60f3\u95ee\u4e00\u4e0b\u5927\u5bb6\u4f7f\u7528\u8bcd\u5178\u7684\u751f\u8bcd\u672c\u529f\u80fd\u65f6\u6709\u6ca1\u6709\u4ee5\u4e0b\u7684\u56f0\u6270: 1.\u5b57\u4f53\u5f88\u4e11; 2.\u6253\u5370\u51fa […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[16],"class_list":["post-217","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql_server_2008_r2"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=217"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}