将XML解析为SQL参数，DateTime2和BIT问题

Question

我有一个难题我无法解决的问题，一个一般的问题：我正在从供应商中解析XML文件，无法修改API输出。多亏了我在这里进行的所有出色的人编写代码以将数据转储到SQL Server 2008 R2中。我无法确定如何完成的一件事是将<date>节点从当前表单转换为datetime2（或您可以推荐的任何datetime格式）。我尝试了许多不同的方法，每个方法都比以前的尝试更惨烈。

我得到的错误是

错误将数据类型nvarchar转换为dateTime2

在单独的音符上，我认为必须有一种更简单的方法来循环通过子节点。还有15个文本字段，如果我可以让SQL Server以某种方式进行繁重的举重，那么我不应该能够循环浏览每个子注释并将姓名/值对作为字符串参数发送？

预先感谢。

XML：

<results>
    <result>
        <title>Application Developer</title>
    ...
    <date>Thu, 30 Jan 2014 14:09:00 GMT</date>
    <expired>false</expired>
</result>

SP：

INSERT INTO dbo.StaffWriteImport
          ( 
        title ,
        ...
        jbdate ,
        expired 
       ) 
     VALUES 
      ( 
     @title ,
     ...
     @jbdate ,
     CAST(CASE @expired WHEN 1 THEN 1 ELSE 0 END AS BIT)   
          ) 

c＃：

using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
        {
            using (Stream dataStream = response.GetResponseStream())
            {
                using (StreamReader reader = new StreamReader(dataStream))
                {
                    string responseFromServer = reader.ReadToEnd();
                    XmlDocument xmlDoc = new XmlDocument();
                    xmlDoc.LoadXml(responseFromServer);
                    XmlNodeList Xn = xmlDoc.SelectNodes("//response/results/result");
                    using (SqlConnection conn = new SqlConnection("data source=myconnectionstring yada yada yada;"))
using (SqlCommand cmd = new SqlCommand(sqlStmt, conn))
{
    foreach (XmlNode xnode in Xn)
    {
        XmlElement resultElement = (XmlElement) xnode;
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        if(!string.IsNullOrEmpty(resultElement.GetElementsByTagName("title")[0].InnerText))
        { cmd.Parameters.AddWithValue("title", resultElement.GetElementsByTagName("title")[0].InnerText); }  
        ...
        if (!string.IsNullOrEmpty(resultElement.GetElementsByTagName("date")[0].InnerText))
        {   var dateformat = "yyyy-MM-dd HH:mm:ss:fff";
            cmd.Parameters.AddWithValue("jbdate", resultElement.GetElementsByTagName("date")[0].InnerText); }
        if (!string.IsNullOrEmpty(resultElement.GetElementsByTagName("expired")[0].InnerText))
        { 
            int exprd = 0;
            if (resultElement.GetElementsByTagName("expired")[0].InnerText == "true") { exprd = 1; }
            cmd.Parameters.AddWithValue("expired", exprd) ; }
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        conn.Close();
    }
}
}}}

Answer 1

这就是我的做法：

1. 我将使我的存储过程使用表有价值的参数立即获取所有结果。此功能从SQL 2008开始，因此您可以使用它。
2. 我将使用Xlinq进行解析，然后创建结果的数据（需要传递到以下值参数的存储过程）。

SQL

我在这里只使用了3列，例如，您可以添加所有需要的内容。

-- This is needed to use as a parameter in an SP
CREATE TYPE XmlResultType AS TABLE 
(
    Title varchar(50),
    [Date] datetime2(7),
    Expired bit
)
GO
-- Simple stored procedure just copies results from TVP into actual table
Create Procedure usp_InsertXmlResultsByTable
(
    @XmlResultTable dbo.XmlResultType READONLY
) 
As
    Insert Into XmlResult
    Select Title, [Date], Expired
    From @XmlResultTable

和示例C＃：

请注意，我知道您在调用Web服务，所以我的示例，想象一下xml变量是您的Web服务调用的结果。在任何一种情况下，您都将使用XDocument.Parse(xml)来创建XDocument对象。

        string xml = @"
            <results>
                <result>
                    <title>Application Developer</title>
                    <date>Thu, 30 Jan 2014 14:09:00 GMT</date>
                    <expired>false</expired>
                </result>
                <result>
                    <title>Incomplete</title>
                    <date></date>
                    <expired></expired>
                </result>
                <result>
                    <title>Professional Time-waster</title>
                    <date>Fri, 31 Jan 2014 18:35:00 GMT</date>
                    <expired>false</expired>
                </result>
            </results>";
        XDocument xDoc = XDocument.Parse(xml);
        // Create a DataTable that mimics the Table Valued Parameter structure.
        DataTable dt = new DataTable();
        dt.Columns.Add("Title", typeof(string));
        dt.Columns.Add("Date", typeof(DateTime));
        dt.Columns.Add("Expired", typeof(bool));

        // Just make the code cleaner
        Func<string, string> getString = s => string.IsNullOrEmpty(s) ? null : s;
        Func<string, DateTime?> getDateTime = d => string.IsNullOrEmpty(d) ? null : (DateTime?)DateTime.Parse(d);
        Func<string, bool?> getBool = b => string.IsNullOrEmpty(b) ? null : (bool?)Convert.ToBoolean(b);
        // here we populate the table variable, using above Funcs<> to handle nulls
        // This code does assume that your date format, when present, will always be parseable.
        xDoc.Descendants("result")
            .ToList()
            .ForEach(r =>
                dt.Rows.Add(
                    getString(r.Element("title").Value),
                    getDateTime(r.Element("date").Value),
                    getBool(r.Element("expired").Value)
                )
            );
        // Now all that's left is to call the stored procedure and pass the DataTable to it
        string connString = "your connection string";
        using(var conn = new SqlConnection(connString))
        using(var cmd = new SqlCommand())
        {
            conn.Open();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_InsertXmlResultsByTable";
            SqlParameter tableParameter = cmd.Parameters.AddWithValue("XmlResultTable", dt);
            tableParameter.SqlDbType = SqlDbType.Structured;
            cmd.ExecuteNonQuery();
            conn.Close();
        }

这是一种略有不同的方法，但是它的性能会更好，因为您只对数据库进行了一个呼叫，以获取从WebService的整个XML有效载荷。我希望这很有帮助！

Answer 2

，因此您无需在应用程序端将其切碎。如果您可以将日期修复为SQL可以读取的内容，则可以将XML传递给SQL，将其切碎并将其插入所需的表中。这具有仅致电存储的Proc

的额外好处

  DECLARE @testXML AS XML = '<Root>
    <node>
    <id>1</id>
    <name>test</name>
    <result>cool</result>
    </node>
    <node>
    <id>2</id>
    <name>sweet</name>
    <result>uncool</result>
    </node>
    </Root>'
      SELECT i.value('(id)[1]','INT') AS id, 
           i.value('(name)[1]','VARCHAR(50)') AS name,
           i.value('(result)[1]','varchar(50)') AS result
    FROM @testXML.nodes('Root/node')x(i)