<i id='0ofdK'><tr id='0ofdK'><dt id='0ofdK'><q id='0ofdK'><span id='0ofdK'><b id='0ofdK'><form id='0ofdK'><ins id='0ofdK'></ins><ul id='0ofdK'></ul><sub id='0ofdK'></sub></form><legend id='0ofdK'></legend><bdo id='0ofdK'><pre id='0ofdK'><center id='0ofdK'></center></pre></bdo></b><th id='0ofdK'></th></span></q></dt></tr></i><div id='0ofdK'><tfoot id='0ofdK'></tfoot><dl id='0ofdK'><fieldset id='0ofdK'></fieldset></dl></div>

<tfoot id='0ofdK'></tfoot><legend id='0ofdK'><style id='0ofdK'><dir id='0ofdK'><q id='0ofdK'></q></dir></style></legend>

如何将数据从 xml 保存到 sql 2008?

时间：2023-06-07

<legend id='bUpm4'><style id='bUpm4'><dir id='bUpm4'><q id='bUpm4'></q></dir></style></legend>

<i id='bUpm4'><tr id='bUpm4'><dt id='bUpm4'><q id='bUpm4'><span id='bUpm4'><b id='bUpm4'><form id='bUpm4'><ins id='bUpm4'></ins><ul id='bUpm4'></ul><sub id='bUpm4'></sub></form><legend id='bUpm4'></legend><bdo id='bUpm4'><pre id='bUpm4'><center id='bUpm4'></center></pre></bdo></b><th id='bUpm4'></th></span></q></dt></tr></i><div id='bUpm4'><tfoot id='bUpm4'></tfoot><dl id='bUpm4'><fieldset id='bUpm4'></fieldset></dl></div>

<tfoot id='bUpm4'></tfoot>

本文介绍了如何将数据从 xml 保存到 sql 2008?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

限时送ChatGPT账号..

如何将数据从 xml 保存到 sql 2008?

How can I save data from xml to sql 2008?

SQL 表:

 [dbo].[Position](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ImoNo] [numeric](8, 0) NOT NULL,
    [sid] [numeric](5, 0) NULL,
    [VesselName] [nvarchar](20) NULL,
    [time] [datetime] NOT NULL,
    [lat] [numeric](9, 2) NULL,
    [lon] [numeric](9, 2) NULL,
    [sog] [numeric](9, 2) NULL,
    [cog] [numeric](9, 2) NULL,
      [hdg] [numeric](9, 2) NULL,
    [eta] [datetime] NULL,
    [NextPort] [nvarchar](20) NULL)

XML 文件:

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"     xmlns:ns1="http://www.fleettracker.de/api/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SOAP-ENV:Body>
<ns1:GetPositionsResponse>
  <body>
    <result>Found 2 vessels.</result>
    <success>true</success>
    <shipsWithPositions xsi:type="ns1:FleettrackerShip">
      <ns1:imono>9456159</ns1:imono>
      <ns1:sid>780</ns1:sid>
      <ns1:name>Trenta</ns1:name>
      <ns1:charterShipName>Trenta</ns1:charterShipName>
      <ns1:pasttrack>
        <lat>1832900</lat>
        <lon>7570400</lon>
        <timestamp>2014-01-14T08:28:45Z</timestamp>
        <orderNumber>0</orderNumber>
        <sog>9.5</sog>
        <cog>22</cog>
        <hdg>22</hdg>
        <eta>2014-01-15T12:00:00</eta>
        <nextport>KWANGYANG</nextport>
      </ns1:pasttrack>
      <ns1:pasttrack>
        <lat>1872560</lat>
        <lon>7589000</lon>
        <timestamp>2014-01-14T07:00:00Z</timestamp>
        <orderNumber>1</orderNumber>
        <sog>10.8</sog>
        <cog>25</cog>
        <hdg>25</hdg>
      </ns1:pasttrack>
    </shipsWithPositions>
 <shipsWithPositions xsi:type="ns1:FleettrackerShip">
      <ns1:imono>9144055</ns1:imono>
      <ns1:sid>789</ns1:sid>
      <ns1:name>Vipava</ns1:name>
      <ns1:charterShipName>Vipava</ns1:charterShipName>
      <ns1:pasttrack>
        <lat>1757160</lat>
        <lon>7536240</lon>
        <timestamp>2014-01-13T19:00:00Z</timestamp>
        <orderNumber>2</orderNumber>
        <sog>9.4</sog>
        <cog>21</cog>
        <hdg>21</hdg>
      </ns1:pasttrack>
      <ns1:pasttrack>
        <lat>1658200</lat>
        <lon>7476480</lon>
        <timestamp>2014-01-13T07:00:00Z</timestamp>
        <orderNumber>3</orderNumber>
        <sog>8.4</sog>
        <cog>29</cog>
        <hdg>29</hdg>
      </ns1:pasttrack>
      <ns1:pasttrack>
        <lat>1630000</lat>
        <lon>7455400</lon>
        <timestamp>2014-01-13T03:00:03Z</timestamp>
        <orderNumber>4</orderNumber>
        <sog>8.83</sog>
        <cog>34</cog>
        <hdg>34</hdg>
        <eta>2014-01-15T08:00:00</eta>
        <nextport>KWANGYANG</nextport>
      </ns1:pasttrack>
    </shipsWithPositions>
  </body>
</ns1:GetPositionsResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>

我尝试使用此查询，但收到错误消息

I tried with this query, but I get error msg

'XQuery [nodes()]: The names "SOAP-ENV" and "ns1:" do not denote a namespace.'

DECLARE @xml XML
DECLARE @character VARCHAR(MAX)

SELECT @character = x.y
FROM OPENROWSET( BULK 'C:\Users\Nale\Desktop\POS.xml', SINGLE_CLOB ) x(y)

-- Fix up the ampersand
SELECT @xml = REPLACE( @character, '&', '&amp;' )


-- Get the tally information
SELECT 
x.y.value('ns1:imono/text())[1]', 'NUMERIC (8,0)') ImoNo,
x.y.value('ns1:sid/text())[1]', 'NUMERIC (5,0)') sid,
x.y.value('ns1:VesselName/text())[1]', 'NVARCHAR (20)') VesselName,
x.y.value('ns1:pasttrack/time/text())[1]', 'DATETIME') time,
x.y.value('ns1:pasttrack/lat/text())[1]', 'NUMERIC (9,2)') lat,
x.y.value('ns1:pasttrack/lon/text())[1]', 'NUMERIC (9,2)') lon,
x.y.value('ns1:pasttrack/sog/text())[1]', 'NUMERIC (9,2)') sog,
x.y.value('ns1:pasttrack/cog/text())[1]', 'NUMERIC (9,2)') cog,
x.y.value('ns1:pasttrack/hdg/text())[1]', 'NUMERIC (9,2)') hdg,
x.y.value('ns1:pasttrack/eta/text())[1]', 'DATETIME') eta,
x.y.value('ns1:pasttrack/NextPort/text())[1]', 'NVARCHAR (20)') NextPort
FROM @xml.nodes('SOAP-ENV:Envelope/SOAP-ENV:Body/ns1:GetPositionsResponse/body/shipsWithPositions') AS x(y)

XML 文件在本地磁盘上.

XML file is on local disk.

我会使用一些 sql 查询，或者将数据从 xml 保存到 sql 表的最佳方法是什么?

Will I use some sql query, or what is the best way to save data from xml to sql table?

推荐答案

使 XML 有效

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"     xmlns:ns1="http://www.fleettracker.de/api/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Body>
        <ns1:GetPositionsResponse>
            <body>
                <result>Found 2 vessels.</result>
                <success>true</success>
                <shipsWithPositions xsi:type="ns1:FleettrackerShip">
                    <ns1:imono>9456159</ns1:imono>
                    <ns1:sid>780</ns1:sid>
                    <ns1:name>Trenta</ns1:name>
                    <ns1:charterShipName>Trenta</ns1:charterShipName>
                    <ns1:pasttrack>
                        <lat>1832900</lat>
                        <lon>7570400</lon>
                        <timestamp>2014-01-14T08:28:45Z</timestamp>
                        <orderNumber>0</orderNumber>
                        <sog>9.5</sog>
                        <cog>22</cog>
                        <hdg>22</hdg>
                        <eta>2014-01-15T12:00:00</eta>
                        <nextport>KWANGYANG</nextport>
                    </ns1:pasttrack>
                    <ns1:pasttrack>
                        <lat>1872560</lat>
                        <lon>7589000</lon>
                        <timestamp>2014-01-14T07:00:00Z</timestamp>
                        <orderNumber>1</orderNumber>
                        <sog>10.8</sog>
                        <cog>25</cog>
                        <hdg>25</hdg>
                    </ns1:pasttrack>
                </shipsWithPositions>
                <shipsWithPositions xsi:type="ns1:FleettrackerShip">
                    <ns1:imono>9144055</ns1:imono>
                    <ns1:sid>789</ns1:sid>
                    <ns1:name>Vipava</ns1:name>
                    <ns1:charterShipName>Vipava</ns1:charterShipName>
                    <ns1:pasttrack>
                        <lat>1757160</lat>
                        <lon>7536240</lon>
                        <timestamp>2014-01-13T19:00:00Z</timestamp>
                        <orderNumber>2</orderNumber>
                        <sog>9.4</sog>
                        <cog>21</cog>
                        <hdg>21</hdg>
                    </ns1:pasttrack>
                    <ns1:pasttrack>
                        <lat>1658200</lat>
                        <lon>7476480</lon>
                        <timestamp>2014-01-13T07:00:00Z</timestamp>
                        <orderNumber>3</orderNumber>
                        <sog>8.4</sog>
                        <cog>29</cog>
                        <hdg>29</hdg>
                    </ns1:pasttrack>
                    <ns1:pasttrack>
                        <lat>1630000</lat>
                        <lon>7455400</lon>
                        <timestamp>2014-01-13T03:00:03Z</timestamp>
                        <orderNumber>4</orderNumber>
                        <sog>8.83</sog>
                        <cog>34</cog>
                        <hdg>34</hdg>
                        <eta>2014-01-15T08:00:00</eta>
                        <nextport>KWANGYANG</nextport>
                    </ns1:pasttrack>
                </shipsWithPositions>
            </body>
        </ns1:GetPositionsResponse>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

2.为您的查询添加命名空间

2. Add namespaces to your query

DECLARE @xml XML
DECLARE @character VARCHAR(MAX)

SELECT @xml = x.y
FROM OPENROWSET( BULK 'C:\Users\Nale\Desktop\POS.xml', SINGLE_CLOB ) x(y)


-- Get the tally information
;WITH XMLNAMESPACES  (
    'http://www.fleettracker.de/api/1.0' as ns1,
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
    'http://schemas.xmlsoap.org/soap/envelope/' AS e
)
SELECT
    x.y.value('(ns1:imono/text())[1]', 'NUMERIC (8,0)') ImoNo,
    x.y.value('(ns1:sid/text())[1]', 'NUMERIC (5,0)') sid,
    x.y.value('(ns1:VesselName/text())[1]', 'NVARCHAR (20)') VesselName,
    x.y.value('(ns1:pasttrack/time/text())[1]', 'DATETIME') time,
    x.y.value('(ns1:pasttrack/lat/text())[1]', 'NUMERIC (9,2)') lat,
    x.y.value('(ns1:pasttrack/lon/text())[1]', 'NUMERIC (9,2)') lon,
    x.y.value('(ns1:pasttrack/sog/text())[1]', 'NUMERIC (9,2)') sog,
    x.y.value('(ns1:pasttrack/cog/text())[1]', 'NUMERIC (9,2)') cog,
    x.y.value('(ns1:pasttrack/hdg/text())[1]', 'NUMERIC (9,2)') hdg,
    x.y.value('(ns1:pasttrack/eta/text())[1]', 'DATETIME') eta,
    x.y.value('(ns1:pasttrack/NextPort/text())[1]', 'NVARCHAR (20)') NextPort
FROM @xml.nodes('e:Envelope/e:Body/ns1:GetPositionsResponse/body/shipsWithPositions') AS x(y)

这篇关于如何将数据从 xml 保存到 sql 2008?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！

上一篇：XML Parsing - Illegal XML Character(在执行存储过程时， 下一篇：匹配嵌套 XML 节点内容的 SQL

相关文章

最新文章

<i id='gvN4R'><tr id='gvN4R'><dt id='gvN4R'><q id='gvN4R'><span id='gvN4R'><b id='gvN4R'><form id='gvN4R'><ins id='gvN4R'></ins><ul id='gvN4R'></ul><sub id='gvN4R'></sub></form><legend id='gvN4R'></legend><bdo id='gvN4R'><pre id='gvN4R'><center id='gvN4R'></center></pre></bdo></b><th id='gvN4R'></th></span></q></dt></tr></i><div id='gvN4R'><tfoot id='gvN4R'></tfoot><dl id='gvN4R'><fieldset id='gvN4R'></fieldset></dl></div>

<legend id='gvN4R'><style id='gvN4R'><dir id='gvN4R'><q id='gvN4R'></q></dir></style></legend>

<tfoot id='gvN4R'></tfoot>