November 26, 2003 at 3:17 pm
I am having problems getting the CRDATA for the FIELD field to come up with OPEN XML.
Here is my data string:
<!DOCTYPE REQUEST SYSTEM "J:\Beau\OSC Common Format\REQUEST Valid & Well-formed.dtd">
<REQUEST>
<ACCT>6132</ACCT>
<ADD1>5 SUMMER STREET</ADD1>
<ADD2/>
<CITY>LANCASTER</CITY>
<FNAME>MAUREEN INGERSON</FNAME>
<LNAME/>
<LTRACCT/>
<LTRFIELDS/>
<LTRNUM>100</LTRNUM>
<STATE>NH</STATE>
<UDKEY>LINK1 6132 PDFTP00 100</UDKEY>
<USER>DAV</USER>
<ZIP>03584</ZIP>
<LETTER>
<FIELD NAME="DBACCT">6132</FIELD>
<FIELD NAME="TODAY">OCT 20 2003</FIELD>
<FIELD NAME="CLIENT TOTAL">3600.00</FIELD>
<FIELD NAME="DBFIRST">MAUREEN</FIELD>
<FIELD NAME="CLNAME">CLIENT HOLDS TU TEST ACCTS</FIELD>
<FIELD NAME="ACCTBAL">3600.00</FIELD>
<FIELD NAME="NOTE1">70.00</FIELD>
<FIELD NAME="NOTE2">Y</FIELD>
<FIELD NAME="NOTE3"/>
<FIELD NAME="NOTE4"/>
<FIELD NAME="NOTE5"/>
<FIELD NAME="COLLECTOR NAME"/>
<LINELIST NAME="CLIENT LIST">
<LINE NUMBER="1">CLIENT HOLDS TU TEST ACCT 3600.00</LINE>
<LINE NUMBER="2"/>
<LINE NUMBER="3"/>
</LINELIST>
</LETTER>
</REQUEST>
Here is my OPEN XML code. I have tried so many different things but it never grabs the value of FIELD.
select *
from OPENXML(@iDocumentHandle,'/REQUEST/LETTER/FIELD',2)
WITH (FIELD varchar(20) '../FIELD',
NAME varchar(20) '@NAME')
And finally the results
FIELD NAME
-------------------- --------------------
6132 DBACCT
6132 TODAY
6132 CLIENT TOTAL
6132 DBFIRST
6132 CLNAME
6132 ACCTBAL
6132 NOTE1
6132 NOTE2
6132 NOTE3
6132 NOTE4
6132 NOTE5
6132 COLLECTOR NAME
(12 row(s) affected)
I am relatively new to XML using SQL and would welcome any advice.
November 27, 2003 at 2:02 am
bodozer,
Try replacing your openxml with the following:
select *
from OPENXML(@iDocumentHandle,'/REQUEST/LETTER/FIELD',2)
WITH (FIELD varchar(20) '.',
NAME varchar(20) '@NAME')
Replacing '../FIELD' with '.' works for me using your example. Hope that helps!
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
November 27, 2003 at 2:12 am
Also, I noticed that some of your FIELD values will be truncated as you allow for varchar(20) in the openxml statement, but some of the FIELD values in the XML doc have more than 20 characters. This might be intended, but thought I'd mention just in case.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply