如何将 xml.nodes() 的输出插入表中? [英] How do I insert output from xml.nodes() into a table?

问题描述

我是 sql server 的新手，我正在尝试将行从 XML 文档插入到数据库中.我做了一些研究，并设法使用 XML 节点函数将 XML 放入行集.但是，我不知道接下来要做什么.如何将此行集插入到具有相同列名的现有表中?

I am new to sql server and I am trying to insert rows into a database from XML documents. I have done some research and managed to get XML into a rowset using the XML nodes function. However, I have no idea what to do next. How do I insert this rowset into an existing table with the same column names?

以下是我目前所拥有的，有人可以帮助我下一步去哪里吗?

Below is what I have so far, can anyone help with where I go next?

DECLARE @xml xml
SET @xml = 
N'  <Products>
        <Product>
        <id>4</id>
        <name>Amy</name>
        <age>25</age>
        </Product>
        <Product>
            <id>7</id>
            <name>Vicky</name>
            <age>40</age>
        </Product>
    </Products>'

SELECT  doc.col.value('id[1]',      'nvarchar(10)')     id
    ,   doc.col.value('name[1]',    'varchar(100)')     name 
    ,   doc.col.value('age[1]',     'nvarchar(10)')     age 
FROM    @xml.nodes('/Products/Product') doc(col)

推荐答案

你需要将SELECT语句的输出传递到表的INSERT语句中您希望填充数据.

You need to pass the output of the SELECT statement to the INSERT statement of the table into which you would like the data to be populated.

建议:

• 您可以将用于 id 和 age 节点的数据类型从 nvarchar(10) 更改为 int 假设两个字段都应表示数字.

• You can change the datatype used for id and age nodes from nvarchar(10) to int assuming that both fields should represent numbers.

脚本:

CREATE  TABLE dbo.mytable
(   
        id      INT         NOT NULL
    ,   name    VARCHAR(30) NOT NULL
    ,   age     INT         NULL
)

DECLARE @xml xml
SET @xml = 
N'  <Products>
        <Product>
        <id>4</id>
        <name>John</name>
        <age>25</age>
        </Product>
        <Product>
            <id>7</id>
            <name>Jane</name>
            <age>40</age>
        </Product>
        <Product>
            <id>6</id>
            <name>Jill</name>
            <age></age>
        </Product>
    </Products>'

INSERT INTO dbo.mytable (id, name, age)
SELECT  doc.col.value('id[1]',      'int')      id
    ,   doc.col.value('name[1]',    'varchar(100)')     name 
    ,   doc.col.value('age[1]',     'int')      age 
FROM    @xml.nodes('/Products/Product') doc(col);

SELECT * FROM dbo.mytable;

输出:

id  name    age
--  ------  ---
4   John    25
7   Jane    40
6   Jill    0

这篇关于如何将 xml.nodes() 的输出插入表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！