关于OPENXML多级联插入数据库的有关问题

2013-10-23

关于OPENXML多级联插入数据库的问题XML格式：RowsRowCustomerNumLZY/CustomerNumProNum1177/Pro

关于OPENXML多级联插入数据库的问题
XML格式：
<Rows>
  <Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501/B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>5</DM>
        <Qty>0</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>6</DM>
        <Qty>0</Qty>
      </EURActualMonth>
    </Values>
  </Row>
<Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501/B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>

        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>5</DM>
        <Qty>0</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>6</DM>
        <Qty>0</Qty>
      </EURActualMonth>
    </Values>
  </Row>
</Rows>

Row是主表数据；Row下的Values是子表数据，子表数据关联主表数据的ID，所以只有先插入主表后再去除ID并将多个子表数据插入到子表，请问如何实现，用游标？思路现在有点乱... 数据库 openxml 数据 xml
[解决办法]
经过测试，这样的代码可行：

DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument  =N'<Rows>
  <Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
<DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
<EURActualMonth>
<MonID>0</MonID>
<RowID>0</RowID>
<DY>2013</DY>
<DM>5</DM>
<Qty>0</Qty>
</EURActualMonth>

    </Values>
  </Row>
</Rows>'


EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
SELECT    *
FROM      OPENXML (@XmlDocumentHandle, '/Rows/Row',2)
           WITH (CustomerNum varchar(50),ProNum int,Abbreviation varchar(50))
SELECT    *
FROM OPENXML (@XmlDocumentHandle, '/Rows/Row/Values/EURActualMonth',2)
     WITH (MonID varchar(10),RowID varchar(10), DM varchar(10),Qty varchar(10))
           
EXEC sp_xml_removedocument @XmlDocumentHandle

/*
CustomerNumProNumAbbreviation
LZY1177RC2094501B
*/

/*
MonIDRowIDDMQty
001127500
00280000
003100000
00472500
0050
*/

[解决办法]


create table 主表
(CustomerNum varchar(10),
 ProNum varchar(10),
 Abbreviation varchar(20))

create table 子表
(MonID int,
 RowID int,
 DY int,
 DM int, 
 
 Qty int)


declare @x xml

select @x='
<Rows>
  <Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501/B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>5</DM>
        <Qty>0</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>6</DM>
        <Qty>0</Qty>
      </EURActualMonth>
    </Values>
  </Row>
<Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501/B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
      <EURActualMonth> 
 
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>5</DM>
        <Qty>0</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>6</DM>
        <Qty>0</Qty>
      </EURActualMonth>
    </Values>
  </Row>
</Rows>'

insert into 主表(CustomerNum,ProNum,Abbreviation)
select o.value('CustomerNum[1]','varchar(10)') 'CustomerNum',
       o.value('ProNum[1]','varchar(10)') 'ProNum',
       o.value('Abbreviation[1]','varchar(20)') 'Abbreviation'
from (select @x 'x') t1
cross apply x.nodes('/Rows/Row') t2(o)

insert into 子表(MonID,RowID,DY,DM,Qty)
select o.value('MonID[1]','int') 'MonID',
       o.value('RowID[1]','int') 'RowID',
       o.value('DY[1]','int') 'DY',
       o.value('DM[1]','int') 'DM',
       o.value('Qty[1]','int') 'Qty'
from (select @x 'x') t1
cross apply x.nodes('/Rows/Row/Values/EURActualMonth') t2(o)


select CustomerNum,ProNum,Abbreviation from 主表

/*
CustomerNum ProNum     Abbreviation
----------- ---------- --------------------
LZY         1177       RC2094501/B
LZY         1177       RC2094501/B

(2 row(s) affected)
*/


select MonID,RowID,DY,DM,Qty from 子表

/*
MonID       RowID       DY          DM          Qty
----------- ----------- ----------- ----------- -----------
0           0           2013        1           127500
0           0           2013        2           80000
0           0           2013        3           100000
0           0           2013        4           72500
0           0           2013        5           0
0           0           2013        6           0
0           0           2013        1           127500
0           0           2013        2           80000
0           0           2013        3           100000
0           0           2013        4           72500
0           0           2013        5           0
0           0           2013        6           0 
 

(12 row(s) affected)
*/

[解决办法]
XML中2个RowID都是0,插入数据表又要是自增的?这样怎么区分?
[解决办法]

DROP TABLE 主表, 子表
create table 主表
(
id INT IDENTITY(1,1) PRIMARY KEY,
CustomerNum varchar(10),
ProNum varchar(10),
Abbreviation varchar(20)
)
 
create table 子表
(
id INT IDENTITY(1,1) PRIMARY KEY,
主表ID INT,
MonID int,
RowID int,
DY int,
DM int,
Qty int
 )

--TRUNCATE TABLE 主表
--TRUNCATE TABLE 子表
 
declare @x xml
 
select @x='
<Rows>
  <Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501/B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>5</DM>
        <Qty>0</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>6</DM>
        <Qty>0</Qty>
      </EURActualMonth>
    </Values>
  </Row>
<Row>
    <CustomerNum>LZY</CustomerNum>
    <ProNum>1177</ProNum>
    <Abbreviation>RC2094501/B</Abbreviation>
    <Values>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>1</DM>
        <Qty>127500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>2</DM>
        <Qty>80000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY> 
 
        <DM>3</DM>
        <Qty>100000</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>4</DM>
        <Qty>72500</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>5</DM>
        <Qty>0</Qty>
      </EURActualMonth>
      <EURActualMonth>
        <MonID>0</MonID>
        <RowID>0</RowID>
        <DY>2013</DY>
        <DM>6</DM>
        <Qty>0</Qty>
      </EURActualMonth>
    </Values>
  </Row>
</Rows>'

DECLARE @identityID TABLE
(
id INT
)
DECLARE @temp TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
CustomerNum NVARCHAR(100),
ProNum INT,
Abbreviation NVARCHAR(255),
sub xml
)
insert into @temp(CustomerNum, ProNum, Abbreviation, sub)
select o.value('CustomerNum[1]','varchar(10)') 'CustomerNum',
       o.value('ProNum[1]','varchar(10)') 'ProNum',
       o.value('Abbreviation[1]','varchar(20)') 'Abbreviation',
   o.query('./Values')
from (select @x 'x') t1
cross apply x.nodes('/Rows/Row') t2(o)

INSERT INTO 主表(CustomerNum, ProNum, Abbreviation)
OUTPUT INSERTED.ID
INTO @identityID(id)
SELECT CustomerNum, ProNum, Abbreviation
FROM @temp
ORDER BY id

insert into 子表(主表id, MonID,RowID,DY,DM,Qty)
SELECT
[主表id] = a.id,--主表id从这儿取了出来
o.value('MonID[1]','int') 'MonID',
row_number() over(PARTITION BY a.id order by getdate()), --这儿也可以用: o.value('RowID[1]','int') 'RowID'
o.value('DY[1]','int') 'DY',
o.value('DM[1]','int') 'DM',
o.value('Qty[1]','int') 'Qty' 
FROM (SELECT rowid = ROW_NUMBER() OVER(ORDER BY id), * FROM @identityID) A
INNER JOIN @temp B
ON A.rowid = b.id
cross apply 
B.sub.nodes('/Values/EURActualMonth') t2(o)


select * from 主表
/*
idCustomerNumProNumAbbreviation
1LZY1177RC2094501/B
2LZY1177RC2094501/B
*/
select * from 子表
/*
id主表IDMonIDRowIDDYDMQty
110120131127500
21022013280000
310320133100000
41042013472500
5105201350
6106201360
720120131127500
82022013280000
920320133100000
102042013472500
11205201350
12206201360
*/

热点排行

SQL Server

关于OPENXML多级联插入数据库的有关问题