为 SQL 2005 寻找一个好的 Bulk Insert XML Shredding 示例 [英] Looking for a good Bulk Insert XML Shredding example for SQL 2005

问题描述

需要一点帮助.我收到一个与此类似的 xml 文件:

A little help needed. I'm receiving an xml file similar to this:

<?xml version="1.0" encoding="utf-16"?>
<dc:GRANTEE xsi:schemaLocation="http://www.blahblahblah.com/FullSchema test.xsd " xmlns:dc="http://www.blahblahblah.com/FullSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RPGID>90CU0024</RPGID>
    <PLANID>01</PLANID>
    <CASE>
        <CASEID>100001</CASEID>
        <RPGID>90CU0024</RPGID>
        <FILE_O>2008-02-08T00:00:00</FILE_O>
        <ADULT>
            <ADULTID>100001A1</ADULTID>
            <CASEID>100001</CASEID>
            <APRIMARY>1</APRIMARY>
            <ARLTNSHP>BM</ARLTNSHP>
            <ADOB>1978-12-03T00:00:00</ADOB>
            <ARACAI>1</ARACAI>
            <ASEX>2</ASEX>
            <SATX>
                <SATXID>MD2120378</SATXID>
                <ADULTID>100001A1</ADULTID>
                <SAASSESS>2008-02-22T00:00:00</SAASSESS>
                <PUBPRVTX>1</PUBPRVTX>
                <TXADMIT>2008-02-23T00:00:00</TXADMIT>
                <TXSET>5</TXSET>
            </SATX>
        </ADULT>
        <CHILD>
            <CHILDID>100001C1</CHILDID>
            <CASEID>100001</CASEID>
            <CINDEX>1</CINDEX>
            <CHBDATE>2008-02-05T00:00:00</CHBDATE>
            <CHSEX>1</CHSEX>
            <CHRACAI>0</CHRACAI>
            <MALTX>
                <MALTXID>10000023</MALTXID>
                <CHILDID>100001C1</CHILDID>
                <RPTDT>2008-02-05T00:00:00</RPTDT>
                <CHMAL1>2</CHMAL1>
             </MALTX>
         </CHILD>
    </CASE>

我需要做的是将其导入并使用存储过程将其切碎到表中.除了在线书籍中的示例的一般反刍之外，我找不到任何其他东西，呃，在线.我需要的是一些语法演练，它展示了如何批量插入文件(使用 OPENROWSET?)，然后获取所有数据并将其拆分到匹配的表中，同时维护键.看起来应该很容易，但参考资料很难获得.如果需要，我也可以访问架构，并且也可以进行注释.

What I need to do is import it and shred it to tables with a stored proc. I've been unable to find anything other than general regurgitation of the examples in the books online, uh, online. what I need is a little syntax walk-through that shows how I can bulk insert the file (using OPENROWSET?) and then take all of the data and split it to matching tables while maintaining keys. It seems like it should be easy, but the reference material is just hard to come by. I also have access to schema if I need that and can annotate as well.

有人有好的参考资料吗?

Anybody have a good reference?

推荐答案

您可以使用带有 RAW 关键字的 BULK INSERT 语句将数据加载到具有单列的表中.它会将整个文档放入一行.然后查询表，将数据放入数据类型为XML的变量中.

You can use the BULK INSERT statement with the RAW keyword to load the data into a table with a single column. It will put the entire document into a single row. Then query the table and put the data into a variable with a datatype of XML.

这个博客文章展示了如何从 XML 文档中获取数据.

This blog post shows how to get data out of the XML document.

这篇关于为 SQL 2005 寻找一个好的 Bulk Insert XML Shredding 示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！