存储过程不在表中存储数据

My problem is that when I execute my stored procedure, the XML data is not saved in my table. I do not receive any error code :((

我的问题是,当我执行我的存储过程时,XML数据不会保存在我的表中。我没有收到任何错误代码:((

My xml file:

我的xml文件:

if you have problem reading my xml file below click here to view it online (more understandable)

如果您在阅读我的xml文件时遇到问题,请点击此处在线查看(更易理解)

<?xml version="1.0"?>
<q:quakeml xmlns="http://quakeml.org/xmlns/bed/1.2" xmlns:catalog="http://anss.org/xmlns/catalog/0.1" xmlns:q="http://quakeml.org/xmlns/quakeml/1.2">
<eventParameters publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.quakeml">
<event catalog:datasource="ak" catalog:eventsource="ak" catalog:eventid="11554973" publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/detail/ak11554973.quakeml"><description><type>earthquake name</type><text>5km WSW of North Pole, Alaska</text></description><origin catalog:datasource="ak" catalog:dataid="ak11554973" catalog:eventsource="ak" catalog:eventid="11554973" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/ak11554973/ak/1429291513635/product.xml"><time><value>2015-04-17T17:13:49.000Z</value></time><longitude><value>-147.4577</value></longitude><latitude><value>64.731</value></latitude><depth><value>10700</value><uncertainty>1300</uncertainty></depth><originUncertainty><horizontalUncertainty>400</horizontalUncertainty><preferredDescription>horizontal uncertainty</preferredDescription></originUncertainty><quality><usedPhaseCount>6</usedPhaseCount><standardError>0.04</standardError></quality><evaluationMode>automatic</evaluationMode><creationInfo><creationTime>2015-04-17T17:25:13.635Z</creationTime><version>1</version></creationInfo></origin><magnitude catalog:datasource="ak" catalog:dataid="ak11554973" catalog:eventsource="ak" catalog:eventid="11554973" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/ak11554973/ak/1429291513635/product.xml#magnitude"><mag><value>0.7</value></mag><type>ml</type><originID>quakeml:earthquake.usgs.gov/realtime/product/origin/ak11554973/ak/1429291513635/product.xml</originID><evaluationMode>automatic</evaluationMode><creationInfo><creationTime>2015-04-17T17:25:13.635Z</creationTime></creationInfo></magnitude><preferredOriginID>quakeml:earthquake.usgs.gov/realtime/product/origin/ak11554973/ak/1429291513635/product.xml</preferredOriginID><preferredMagnitudeID>quakeml:earthquake.usgs.gov/realtime/product/origin/ak11554973/ak/1429291513635/product.xml#magnitude</preferredMagnitudeID><type>earthquake</type><creationInfo><agencyID>ak</agencyID><creationTime>2015-04-17T17:25:13.635Z</creationTime><version>1</version></creationInfo></event>
<event catalog:datasource="nn" catalog:eventsource="nn" catalog:eventid="00490511" publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/detail/nn00490511.quakeml"><description><type>earthquake name</type><text>28km SW of Lovelock, Nevada</text></description><origin catalog:datasource="nn" catalog:dataid="nn00490511" catalog:eventsource="nn" catalog:eventid="00490511" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nn00490511/nn/1429291355376/product.xml"><time><value>2015-04-17T17:09:21.386Z</value></time><longitude><value>-118.7333</value></longitude><latitude><value>40.0237</value></latitude><depth><value>0</value><uncertainty>5313.5</uncertainty></depth><originUncertainty><horizontalUncertainty>8118.1</horizontalUncertainty><preferredDescription>horizontal uncertainty</preferredDescription></originUncertainty><quality><usedPhaseCount>13</usedPhaseCount><usedStationCount>11</usedStationCount><standardError>0.1417</standardError><azimuthalGap>245.03</azimuthalGap><minimumDistance>0.591</minimumDistance></quality><evaluationMode>manual</evaluationMode><creationInfo><agencyID>NN</agencyID><creationTime>2015-04-17T17:22:35.376Z</creationTime><version>490511</version></creationInfo></origin><magnitude catalog:datasource="nn" catalog:dataid="nn00490511" catalog:eventsource="nn" catalog:eventid="00490511" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nn00490511/nn/1429291355376/product.xml#magnitude"><mag><value>1.95</value><uncertainty>0.23</uncertainty></mag><type>ml</type><stationCount>8</stationCount><originID>quakeml:earthquake.usgs.gov/realtime/product/origin/nn00490511/nn/1429291355376/product.xml</originID><evaluationMode>manual</evaluationMode><creationInfo><agencyID>NN</agencyID><creationTime>2015-04-17T17:22:35.376Z</creationTime></creationInfo></magnitude><preferredOriginID>quakeml:earthquake.usgs.gov/realtime/product/origin/nn00490511/nn/1429291355376/product.xml</preferredOriginID><preferredMagnitudeID>quakeml:earthquake.usgs.gov/realtime/product/origin/nn00490511/nn/1429291355376/product.xml#magnitude</preferredMagnitudeID><type>earthquake</type><creationInfo><agencyID>nn</agencyID><creationTime>2015-04-17T17:22:35.376Z</creationTime><version>490511</version></creationInfo></event>

<creationInfo><creationTime>2015-04-17T17:32:50.000Z</creationTime></creationInfo>
</eventParameters>
</q:quakeml>

My stored procedure:

我的存储过程:

ALTER PROCEDURE [dbo].[InsertXML]
   @xml XML
AS
BEGIN
    WITH XMLNAMESPACES ('http://quakeml.org/xmlns/quakeml/1.2' as q,
                        default 'http://quakeml.org/xmlns/bed/1.2')
      INSERT INTO quake_tbl
         SELECT
            tim.value('(text())[1]','VARCHAR(300)') AS times,
            latitud.value('(text())[1]','numeric(18,6)') AS latitude ,
            longitud.value('(text())[1]','numeric(18,6)') AS longitude ,
            deph.value('(text())[1]','numeric(18,6)') AS depth ,
            magnitud.value('(text())[1]','numeric(18,6)') AS magnitude ,
            typomag.value('(type/text())[1]','VARCHAR(300)') AS mag_type,
            placee.value('(text())[1]','VARCHAR(300)') AS place ,
            typo.value('(text())[1]','VARCHAR(300)') AS type 
         FROM
            @xml.nodes('/q:quakeml/eventParameters/event') as TEMPTABLE(quakedetails)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('origin/time/value') AS timess(tim)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('origin/latitude/value') AS lat(latitud)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('origin/longitude/value') AS long(longitud)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('origin/depth/value') AS dep(deph)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('magnitude/mag/value') AS magn(magnitud)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('magnitude/type') AS typmag(typomag)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('origin/description/text') AS plac(placee)
         CROSS APPLY 
            TEMPTABLE.quakedetails.nodes('origin/description/type') AS typ(typo)
  END

My vb.net code behind in asp.net:

我在asp.net中的vb.net代码:

 Imports System.IO
 Imports System.Data
 Imports System.Configuration
 Imports System.Data.SqlClient
 Partial Class UploadXml
 Inherits System.Web.UI.Page

Protected Sub UploadXML(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_upload.Click
    Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
    Dim filePath As String = Server.MapPath("~/Uploads/") & fileName
    FileUpload1.SaveAs(filePath)
    Dim xml As String = File.ReadAllText(filePath)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("InsertXML")
            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@xml", xml)
            con.Open()
            cmd.CommandTimeout = 120
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

End Class

My markup in html:

我在html中的标记:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="UploadXml.aspx.vb" Inherits="UploadXml" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:FileUpload ID = "FileUpload1" runat = "server" />
<asp:Button ID="btn_upload" Text="Upload XML" runat="server"  />

    </div>
    </form>
</body>
</html>

1 个解决方案

#1

When you debug your SELECT statement, it becomes obvious that the last two CROSS APPLY statements don't return any data:

调试SELECT语句时,最后两个CROSS APPLY语句显然不返回任何数据:

CROSS APPLY 
    TEMPTABLE.quakedetails.nodes('origin/description/text') AS plac(placee)
CROSS APPLY 
    TEMPTABLE.quakedetails.nodes('origin/description/type') AS typ(typo)

So check your XML - somehow that path origin/description doesn't seem to exist.

所以检查你的XML - 不知何故,路径起源/描述似乎不存在。

If you check your XML, you'll see that the <description> node is directly under the <event> node - not under <origin> - so use this:

如果检查XML,您会看到节点直接位于节点下 - 而不是 - 所以请使用:

CROSS APPLY 
    TEMPTABLE.quakedetails.nodes('description/text') AS plac(placee)
CROSS APPLY 
    TEMPTABLE.quakedetails.nodes('description/type') AS typ(typo)

and then your query should work.

然后你的查询应该工作。

Update: you could optimize your query by not doing as many CROSS APPLY calls to .nodes() functions - limit yourself to just the <origin> nodes, and then grab the individual bits and pieces inside that node using XPath expression - try this query:

更新:您可以通过不对.nodes()函数执行尽可能多的CROSS APPLY调用来优化查询 - 将自己限制为仅仅节点,然后使用XPath表达式获取该节点内的各个位和 - 尝试此查询:

; WITH XMLNAMESPACES ('http://quakeml.org/xmlns/quakeml/1.2' as q,
                      default 'http://quakeml.org/xmlns/bed/1.2')
 SELECT
     Times = XOrigin.value('(time/value)[1]', 'varchar(100)'),
     Latitude = XOrigin.value('(latitude/value)[1]', 'numeric(18,6)'),
     Longitude = XOrigin.value('(longitude/value)[1]', 'numeric(18,6)'),
     Depth = XOrigin.value('(depth/value)[1]', 'numeric(18,6)'),
     Magnitude = XEvent.value('(magnitude/value)[1]', 'numeric(18,6)'),
     Mag_Type = XEvent.value('(magnitude/type/value)[1]', 'varchar(100)'),
     Place = XEvent.value('(description/text)[1]', 'varchar(100)'),
     [Type] = XEvent.value('(description/type)[1]', 'varchar(100)')
 FROM
        @xml.nodes('/q:quakeml/eventParameters/event') as XT1(XEvent)
 CROSS APPLY
        XEvent.nodes('origin') AS XT2(XOrigin)

This is the SELECT only - does that run faster?

这只是SELECT - 运行得更快吗?

#1