Prev: Whitepaper - In-Memory Analytics
Next: use a generated column in a where clause
From: scv1977 on 26 Apr 2010 22:18
Hi
I'm currently conversioning mssql to DB2(V9.7 Fix1)
The MSSQL has sources(SP,Function,XML Parsing)
In the SP(MSSQL) source, exists sp_xml_preparedocument function.
And we convert to DB2 using XMLTABLE,XMLPARSE
So in...
I have the following build problem with CODE executing the following.
The simplified code does the following(SQL16003N)
I think.... Since the XML source has multirow data....
We have to handle the cursor?? or Does The XML function exists for
bulk row proccesing
The Sample code is the following.

1) MSSQL
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

SELECT @CustName = ISNULL(CustName , ''),
@CustSeq = ISNULL(CustSeq , 0)

FROM OPENXML(@docHandle, N'/ROOT/DataBlock1', @xmlFlags)----
>Multi XML data
WITH ( CustName NVARCHAR(200),
CustSeq INT

)

1) DB2
select * from XMLTABLE('$i' PASSING
XMLPARSE(document
'<ROOT>
<DataBlock1>
<WorkingTag>A</WorkingTag>
<IDX_NO>1</IDX_NO>
<Status>0</Status>
<DataSeq>1</DataSeq>
<Selected>1</Selected>
<TABLE_NAME>DataBlock1</TABLE_NAME>
<CustName>CUST1</CustName>
<CustSeq>1</CustSeq>
</DataBlock1>
<DataBlock1>
<WorkingTag>A</WorkingTag>
<IDX_NO>2</IDX_NO>
<Status>0</Status>
<DataSeq>2</DataSeq>
<Selected>1</Selected>
<TABLE_NAME>DataBlock1</TABLE_NAME>
<CustName>CUST2</CustName>
<CustSeq>2</CustSeq>

</DataBlock1>
</ROOT>
') AS "i"
COLUMNS CustName VARCHAR(200) PATH 'ROOT/DataBlock1/CustName',
CustSeq INTEGER PATH 'ROOT/DataBlock1/CustSeq') AS X
 | 
Pages: 1
Prev: Whitepaper - In-Memory Analytics
Next: use a generated column in a where clause