获取要馈送到SQL Server表中的XML

Getting XML to feed into SQL Server table(获取要馈送到SQL Server表中的XML)

问题描述

我正在尝试(目前)获取一个简单的XML来馈送到SQL Server表中。

XML为：

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfSafeEODBalance xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SafeEODBalance>
        <Lane>1</Lane>
        <PouchId>06292019053041001</PouchId>
        <BusinessDay>6/29/2019</BusinessDay>
        <BusinessStartingTime>6/29/2019 5:36:58 AM</BusinessStartingTime>
        <BusinessEndingTime>6/30/2019 12:15:55 AM</BusinessEndingTime>
        <StartingBalance>0.0000</StartingBalance>
        <EndingBalance>8</EndingBalance>
    </SafeEODBalance>
    <SafeEODBalance>
        <Lane>2</Lane>
        <PouchId>06292019053042002</PouchId>
        <BusinessDay>6/29/2019</BusinessDay>
        <BusinessStartingTime>6/29/2019 5:36:58 AM</BusinessStartingTime>
        <BusinessEndingTime>6/30/2019 12:15:55 AM</BusinessEndingTime>
        <StartingBalance>100.0000</StartingBalance>
        <EndingBalance>2</EndingBalance>
    </SafeEODBalance>
</ArrayOfSafeEODBalance>

并保存到C：UserscjDocumentsEodBalance.xml

我已经设置了SQL Server表[dbo].[EndofDay]，该表完全包含以下各项的列：

以下是我正在尝试的查询：

INSERT INTO [dbo].[EndofDay] ([PouchID], [Lane], [BusinessDay], BusinessStartingTime, BusinessEndingTime, [StartingBalance], [EndingBalance])
    SELECT
        MY_XML.SafeEODBalance.query('PouchId').value('.', 'VARCHAR(25)'),
        MY_XML.SafeEODBalance.query('Lane').value('.', 'NCHAR(2)'),
        MY_XML.SafeEODBalance.query('BusinessDay').value('.', 'DATE'),
        MY_XML.SafeEODBalance.query('BusinessStartingTime').value('.', 'DATETIME'),
        MY_XML.SafeEODBalance.query('BusinessEndingTime').value('.', 'DATETIME'),
        MY_XML.SafeEODBalance.query('StartingBalance').value('.', 'NCHAR(10)'),
        MY_XML.SafeEODBalance.query('EndingBalance').value('.', 'NCHAR(10)')
    FROM 
        (SELECT CAST(MY_XML AS XML)
         FROM OPENROWSET(BULK 'C:UserscjDocumentsEodBalance.xml',SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
         CROSS APPLY MY_XML.nodes('SafeEODBalance/SafeEODBalances') AS MY_XML (SafeEODBalance);

当我运行此命令时，我得到：

    (0 rows affected)        

    Completion time: 2019-08-29T16:07:12.3361442-04:00

这显然应该向此填充两行，但它在表中没有提供任何信息。

sql

这里是调整后的工作推荐答案。准备好后，只需取消对插入行的注释。

SQL

WITH XmlFile (xmlData) AS
(
   SELECT CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK 'C:UserscjDocumentsEodBalance.xml', SINGLE_BLOB) AS x
)
--INSERT INTO [dbo].[EndofDay] 
--([PouchID], [Lane], [BusinessDay], BusinessStartingTime, BusinessEndingTime, [StartingBalance], [EndingBalance])
SELECT c.value('(PouchId/text())[1]', 'VARCHAR(25)') AS [PouchId]
   , c.value('(Lane/text())[1]', 'NCHAR(2)') AS [Lane]
   , c.value('(BusinessDay/text())[1]', 'DATE') AS [BusinessDay]
   , c.value('(BusinessStartingTime)[1]', 'datetime') AS [BusinessStartingTime]
   , c.value('(BusinessEndingTime/text())[1]', 'datetime') AS [BusinessEndingTime]
   , c.value('(StartingBalance/text())[1]', 'MONEY') AS [StartingBalance]
   , c.value('(EndingBalance/text())[1]', 'MONEY') AS [EndingBalance]
FROM XmlFile CROSS APPLY xmlData.nodes('/ArrayOfSafeEODBalance/SafeEODBalance') AS t(c);

这篇关于获取要馈送到SQL Server表中的XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！