需要将XML在线文档转换为SQL-Server表

http://www.greaterkashmir.com/feed.aspx?cat_id=2

the above is Online link of xml RSS newsFeed document. i need to retrieve that and convert each data item in SQL Table.

以上是xml RSS newsFeed文档的在线链接。我需要检索它并转换SQL表中的每个数据项。

the kind of solution i am looking is below. in it there is a issue it return NULL

我正在寻找的解决方案如下。在它有一个问题,它返回NULL

How can I to read a XML from a URL using T-SQL?

如何使用T-SQL从URL读取XML?

1 个解决方案

#1

1.Save the aspx page as a xml document.( I have saved it as check.xml)

1.将aspx页面保存为xml文档。(我将其保存为check.xml)

2.Create a table to store XML data as below,

2.创建一个表来存储XML数据,如下所示,

 CREATE TABLE xmlinput
 (
  Id INT IDENTITY PRIMARY KEY,
  XMLData XML,
  createdate DATETIME
  )

3.Insert xmlinput table from the xml file using OPENROWSET as below,

3.使用OPENROWSET从xml文件插入xmlinput表,如下所示,

 INSERT INTO xmlinput(XMLData, createdate)
 SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
 FROM OPENROWSET(BULK '\\DEVSQL2012\SQLServer\check.xml', SINGLE_BLOB) AS x;

4.Now we have to use sp_xml_preparedocument stored procedure (with OPENXML function ) to process the xml data stored in Xmlinput table.

4.现在我们必须使用sp_xml_preparedocument存储过程(使用OPENXML函数)来处理存储在Xmlinput表中的xml数据。

 DECLARE @XML AS XML, @doc AS INT, @SQL NVARCHAR (MAX)

 SELECT @XML = XMLData FROM xmlinput
 --select @XML

 EXEC sp_xml_preparedocument @doc OUTPUT, @XML


 SELECT Category, link, title,description,  pubDate ,guid
 into xmldata

 FROM OPENXML(@doc, 'xml/channel/item')
 WITH 
(
Category [varchar](50)          'category',
link [varchar](100)             'link',
title [varchar](100)            'title',
description [varchar](1000)     'description',
pubDate [varchar](100)          'pubDate',
guid    [varchar](100)          'guid'
)


EXEC sp_xml_removedocument @doc  --Call this sp to remove document cache
GO


select *from xmldata

#1