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
<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/*
CustomerNum ProNum Abbreviation
LZY 1177 RC2094501B
*//*
MonID RowID DM Qty
0 0 1 127500
0 0 2 80000
0 0 3 100000
0 0 4 72500
0 0 5 0
*/
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><EURActualMonth>
<MonID>0</MonID>
<RowID>0</RowID>
<DY>2013</DY>
<DM>6</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
DECLARE @XmlDocument nvarchar(4000)
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>
<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>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>
<EURActualMonth>
<MonID>0</MonID>
<RowID>0</RowID>
<DY>2013</DY>
<DM>6</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 int,RowID int, DM int,Qty int)
EXEC sp_xml_removedocument @XmlDocumentHandle
/*
CustomerNum ProNum Abbreviation
LZY 1177 RC2094501B
LZY 1177 RC2094501B
*/
/*
MonID RowID DM Qty
0 0 1 127500
0 0 2 80000
0 0 3 100000
0 0 4 72500
0 0 5 0
0 0 6 0
0 0 1 127500
0 0 2 80000
0 0 3 100000
0 0 4 72500
0 0 5 0
0 0 6 0
*/
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 xmlselect @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)
*/
谢谢你的耐心解答,好像你这边疏忽了我的意思,我的意思是主表插入之后会自动生成一个RowID,然后我要取出这个RowID,再插入到相应的子表中,重要的是要怎么让插入的RowID对应相应的子表数据
谢谢你的耐心解答,好像你这边疏忽了我的意思,我的意思是主表插入之后会自动生成一个RowID,然后我要取出这个RowID,再插入到相应的子表中,重要的是要怎么让插入的RowID对应相应的子表数据
现在你给的主表数据两行都一样,但实际应用中应该是不存在的吧
实际上应用是有的,比如说我往主表批量插入数据,与主表对应的子表和主表的关系是一对多的关系,那说明我主表的一条数据对应多条子表数据,XML格式就是如上格式,而且整个操作里边包括修改和新增,这个都缕清楚了,就是不知道如何对应这个RowID, 比如上边两条 , 第一个是修改:RowID:2 第二个是新增:RowID:4 我如何将Values的集合对应到相应的2和4下(RowID为自增)。 方便的话能否留个QQ号什么的方便联系嘛?
<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>....</EURActualMonth>
里面应该还包括有<Abbreviation>RC2094501B</Abbreviation>这个,这样父子级之间才能够建立关联
select @x='
<Rows>
<Row>
<RowID>0</RowID>
<CustomerNum>LZY</CustomerNum>
<ProNum>1177</ProNum>
<Abbreviation>RC2094501/B</Abbreviation>
<Values>
<EURActualMonth>
<RowID>0</RowID>
<DY>2013</DY>
<DM>1</DM>
<Qty>127500</Qty>
</EURActualMonth>
<EURActualMonth>
<RowID>0</RowID>
<DY>2013</DY>
<DM>2</DM>
<Qty>80000</Qty>
</EURActualMonth>
</Values>
</Row>
<Row>
<RowID>0</RowID>
<CustomerNum>LZY</CustomerNum>
<ProNum>1200</ProNum>
<Abbreviation>RC2094501/C</Abbreviation>
<Values>
<EURActualMonth>
<RowID>0</RowID>
<DY>2014</DY>
<DM>1</DM>
<Qty>127500</Qty>
</EURActualMonth>
<EURActualMonth>
<RowID>0</RowID>
<DY>2014</DY>
<DM>2</DM>
<Qty>80000</Qty>
</EURActualMonth>
</Values>
</Row>
</Rows>'这个应该是完整的 子表的RowID对应主表里的RowID
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 idinsert 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 主表
/*
id CustomerNum ProNum Abbreviation
1 LZY 1177 RC2094501/B
2 LZY 1177 RC2094501/B
*/
select * from 子表
/*
id 主表ID MonID RowID DY DM Qty
1 1 0 1 2013 1 127500
2 1 0 2 2013 2 80000
3 1 0 3 2013 3 100000
4 1 0 4 2013 4 72500
5 1 0 5 2013 5 0
6 1 0 6 2013 6 0
7 2 0 1 2013 1 127500
8 2 0 2 2013 2 80000
9 2 0 3 2013 3 100000
10 2 0 4 2013 4 72500
11 2 0 5 2013 5 0
12 2 0 6 2013 6 0
*/
select @data
--2、任意级别是否存在price节点
select @data.exist('//price')
--3、获取所有book节点
select @data.query('//book')
--4、获取所有包含lang属性的节点
select @data.query('//*[@lang]')
--5、获取第一个book节点
select @data.query('//book[1]')
--6、获取前两个book节点
select @data.query('//book[position()<=2]')
--7、获取最后一个book节点
select @data.query('//book[last()]')
--8、获取price>35的所有book节点
select @data.query('//book[price>35]')
--9、获取category="WEB"的所有book节点
select @data.query('//book[@category="WEB"]')
--10、获取title的lang="en"的所有book节点
select @data.query('//book/title[@lang="en"]')
--11、获取title的lang="en"且 price>35的所有book节点
select @data.query('//book[./title[@lang="en"] or price>35 ]')
--12、获取title的lang="en"且 price>35的第一book的(第一个)title
select @data.query('//book[./title[@lang="en"] and price>35 ]').value('(book/title)[1]','varchar(max)')
--13、等价于12
select @data.value('(//book[./title[@lang="en"] and price>35 ]/title)[1]','varchar(max)')
--14、获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性
select @data.value('((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]','varchar(max)')
--15、获取第一本书的title
select Tab.Col.value('(book/title)[1]','varchar(max)') as title
from @data.nodes('bookstore')as Tab(Col)
--16、获取每本书的第一个author
select Tab.Col.value('author[1]','varchar(max)') as title
from @data.nodes('//book')as Tab(Col)
--17、获取所有book的所有信息
select
T.C.value('title[1]','varchar(max)') as title,
T.C.value('year[1]','int') as year,
T.C.value('title[1]','varchar(max)')as title,
T.C.value('price[1]','float') as price,
T.C.value('author[1]','varchar(max)') as author1,
T.C.value('author[2]','varchar(max)') as author2,
T.C.value('author[3]','varchar(max)') as author3,
T.C.value('author[4]','varchar(max)') as author4
from @data.nodes('//book') as T(C)
--18、获取不是日语(lang!="jp")且价格大于35的书的所有信息
select
T.C.value('title[1]','varchar(max)') as title,
T.C.value('year[1]','int') as year,
T.C.value('title[1]','varchar(max)')as title,
T.C.value('price[1]','float') as price,
T.C.value('author[1]','varchar(max)') as author1,
T.C.value('author[2]','varchar(max)') as author2,
T.C.value('author[3]','varchar(max)') as author3,
T.C.value('author[4]','varchar(max)') as author4
from @data.nodes('//book[./title[@lang!="jp"] and price>35 ]') as T(C)