在url中有xml文件：

<response>
<sum>0</sum>
<result>0</result>
<comment>sel*1.9488|buy*1.9453</comment>
</response>

现在想要存储过程,我可以从url解析这个xml文件,并更新为< comment>中的列值.ll * 1.9488 | buy * 1.9453< / comment>想添加购买* 1.9453到我的桌子.怎么样？

要从URL获取XML,您需要执行以下操作：

启用Ole Automation Procedures

sp_configure 'show advanced options',1;
GO
RECONfigURE;
GO
sp_configure 'Ole Automation Procedures',1;
GO
RECONfigURE;
GO

然后,为了从URL获取XML(基于here的更新版本回答),下面创建一个临时表来存储该值,以便您可以使用xpath和子字符串处理结果.

这是一个使用google maps xml的工作示例,您需要更新url和xpath以满足您的特定要求.

USE tempdb
GO

IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
CREATE TABLE #xml ( yourXML XML )
GO

DECLARE @URL VARCHAR(8000) 

DECLARE @QS varchar(50)

-- & or ? depending if there are other query strings
-- Use this for when there is other query strings:
SELECT @QS = '&date='+convert(varchar(25),getdate(),126)
-- Use this for when there is NO other query strings:
-- SELECT @QS = '?date='+convert(varchar(25),126)
SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false'  + @QS

DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int 
DECLARE @Result int 
DECLARE @HTTPStatus int 
DECLARE @ErrorMsg varchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp',@Obj OUT 

EXEC @Result = sp_OAMethod @Obj,'open',NULL,'GET',@URL,false
EXEC @Result = sp_OAMethod @Obj,'setRequestHeader','Content-Type','application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj,send,''
EXEC @Result = sp_OAGetProperty @Obj,'status',@HTTPStatus OUT 

INSERT #xml ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj,'responseXML.xml'--,@Response OUT 

SELECT  yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml

为了插入子字符串,您需要执行类似这样的操作以返回管道后的所有内容并添加到表中：

INSERT tableDestination (valueDestination)
SELECT  substring(yourXML.value('(//response/comment)[1]','VARCHAR(MAX)'),charindex('|',yourXML.value('(//response/comment)[1]',1)+1,len(yourXML.value('(//response/comment)','VARCHAR(MAX)'))) from #xml

我想使用LINQ to XML读/写加密的 XML文件.有谁知道如何使用.NET Framework内置的加密算法来加密XDocument对象使用的Stream？

我确实尝试过,但你不能将CryptoStream设置为读/写访问权限.它只支持Read或Write,这会导致LINQ to XML抛出异常.

更新：“动态”读取/写入文档会很好,但我只需要读取加密的xml文件,对其进行操作,然后再将其写回加密.

最简单的方法可能是XDocument.Load(),Linq,然后是XDocument.Save().从快速测试应用程序(轻松使用非处置资源)：

XDocument writeContacts = new XDocument(
   new XElement("contacts",new XElement("contact",new XElement("name","Patrick Hines"),new XElement("phone","206-555-0144",new XAttribute("type","home")),"425-555-0145","work")),new XElement("address",new XElement("street1","123 Main St"),new XElement("city","Mercer Island"),new XElement("state","WA"),new XElement("postal","68042")
         )
      )
   )
);

Rijndael RijndaelAlg = Rijndael.Create();

FileStream writeStream = File.Open("data.xml",FileMode.Create);
CryptoStream cStream = new CryptoStream(writeStream,RijndaelAlg.CreateEncryptor(RijndaelAlg.Key,RijndaelAlg.IV),CryptoStreamMode.Write);

StreamWriter writer = new StreamWriter(cStream);

writeContacts.Save(writer);

writer.Flush();
writer.Close();

FileStream readStream = File.OpenRead("data.xml");

cStream = new CryptoStream(readStream,RijndaelAlg.CreateDecryptor(RijndaelAlg.Key,CryptoStreamMode.Read);

XmlTextReader reader = new XmlTextReader(cStream);

XDocument readContacts = XDocument.Load(reader);

//manipulate with Linq and Save() when needed

将您最喜欢的ICryptoTransform交换到CryptoStream中.

我试图创建一个网页服务,获取一个URL,例如www.domain.co.uk/prices.csv然后读取csv文件.这是可能的吗？理想情况下没有下载csv文件？

你可以使用：

public string GetCSV(string url)
{
    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
    HttpWebResponse resp = (HttpWebResponse)req.GetResponse();

    StreamReader sr = new StreamReader(resp.GetResponseStream());
    string results = sr.ReadToEnd();
    sr.Close();

    return results;
}

然后拆分它：

public static void SplitCSV()
{
    List<string> splitted = new List<string>();
    string fileList = getCSV("http://www.google.com");
    string[] tempStr;

    tempStr = fileList.Split(',');

    foreach (string item in tempStr)
    {
        if (!string.IsNullOrWhiteSpace(item))
        {
            splitted.Add(item);
        }
    }
}

虽然有很多的CSV解析器在那里,我建议不要滚动自己的. FileHelpers是一个很好的.