June 28, 2006 at 7:50 am
So I have my XML doc with whole bunch of data. For this specific INSERT I just need few records. So I try:
DECLARE @number int
SET @number = 1
WHILE @number < 8
BEGIN
INSERT INTO SomeTable
(
UserID,
SomeColumn,
SomeOtherColumn,
SomeThirdColumn
)
SELECT
UserID,
SomeColumn,
SomeOtherColumn,
SomeThirdColumn
FROM OPENXML(@intDoc,'/DATA/ITEM')
WITH (
UserIDint@user-id,
SomeColumnvarchar(100)'Paper' + @number + 'Sheet',
SomeOtherColumnvarchar(50)'Block' + @number + 'Num',
SomeThirdColumnvarchar(50)'License' + @number + 'Valid'
)
SET @number = @number + 1
END
The reason to this as there is N amount of columns in my XML doc that go like this:
Paper1Sheet
Block1Num
License1Valid
Paper2Sheet
Block2Num
License2Valid
Paper3Sheet
Block3Num
License3Valid
...
PaperNNSheet
BlocNNNum
LicenseNNValid
So I'm trying to insert them all into a table regardless of how many there are.
PS: Btw, it doesn't like @user-id inserted like that either.
Hope someone can help me resolve this.
June 29, 2006 at 1:38 pm
just run this example in query analyzer. hope this will help you to proceed with your specifics
-- Start Code
declare @doc nvarchar(4000)
set @doc =
'
<data>
<paper1sheet>value for paper1sheet</paper1sheet>, <block1num>value for block1num</block1num>, <license1valid>value for license1valid</license1valid>
<paper2sheet>value for paper2sheet</paper2sheet>, <block2num>value for block2num</block2num>, <license2valid>value for license2valid</license2valid>
<paper3sheet>value for paper3sheet</paper3sheet>, <block3num>value for block3num</block3num>, <license3valid>value for license3valid</license3valid>
<paper4sheet a="11">value for paper4sheet</paper4sheet>, <block4num>value for block4num</block4num>, <license4valid>value for license4valid</license4valid>
<paper5sheet a="12">value for paper5sheet</paper5sheet>, <block5num>value for block5num</block5num>, <license5valid>value for license5valid</license5valid>
<paper6sheet>value for paper6sheet</paper6sheet>, <block6num>value for block6num</block6num>, <license6valid>value for license6valid</license6valid>
<paper7sheet>value for paper7sheet</paper7sheet>, <block7num>value for block7num</block7num>, <license7valid>value for license7valid</license7valid>
</data>
'
declare @xml table(columnname varchar(100), columnvalue varchar(1000))
declare @dochandle integer
exec sp_xml_preparedocument @dochandle output, @doc
insert into @xml
select
columnname,
columnvalue
from openxml(@dochandle,'/data/*')
with (
columnname varchar(8000) '@mp:localname' ,
columnvalue varchar(8000) './text()'
  as t1
exec sp_xml_removedocument @dochandle
select
replace(replace(t1.columnname,'paper',''),'sheet','') as id,
t1.columnvalue as column1,
t2.columnvalue as column2,
t3.columnvalue as column3
from
@xml as t1
join
@xml as t2
on
replace(replace(t1.columnname,'paper',''),'sheet','') = replace(replace(t2.columnname,'block',''),'num','')
and
isnumeric(replace(replace(t2.columnname,'block',''),'num','')) = 1
join
@xml as t3
on
replace(replace(t1.columnname,'paper',''),'sheet','') = replace(replace(t3.columnname,'license',''),'valid','')
and
isnumeric(replace(replace(t3.columnname,'license',''),'valid','')) = 1
where
isnumeric(replace(replace(t1.columnname,'paper',''),'sheet','')) = 1
-- End Code
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply