openxml with null values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

openxml with null values

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-12-02 : 01:47:27

When using openxml to insert or update records, what is the best way to handle emtpy strings that should be converted to null? I do not wany any empty strings in columns where null values are allowed and / or not allowed. Currently, I'm using the nullif function for each column. Is that my only option?

Example:

XML File:
<frmJE>
<entry_hdr>
<current_row>3</current_row>
<form_action>SAVE</form_action>
<change_ind>Y</change_ind>
<form_name>frmJE</form_name>
<entry_date>10/28/2003</entry_date>
<change_lvl>HDR,DTL</change_lvl>
<entry_id></entry_id>
<create_user></create_user>
<fiscal_period></fiscal_period>
<fiscal_year></fiscal_year>
<last_user>DJONE2</last_user></entry_hdr>
<entry_dtl><unit_nbr>99002</unit_nbr><subdivision>525</subdivision><account>6072</account><aor>219011</aor><amount>1</amount><import_ref>1</import_ref><line_nbr_desc>1</line_nbr_desc><last_user>DJONE2</last_user></entry_dtl>
<entry_dtl><unit_nbr>99002</unit_nbr><subdivision>525</subdivision><account>6072</account><aor>219011</aor><amount>1</amount><import_ref>1</import_ref><line_nbr_desc>1</line_nbr_desc><last_user>DJONE2</last_user></entry_dtl>
<entry_dtl><unit_nbr></unit_nbr><subdivision></subdivision><account></account><aor></aor><amount>0</amount><import_ref></import_ref><line_nbr_desc></line_nbr_desc><last_user>DJONE2</last_user></entry_dtl>
<entry_dtl><unit_nbr></unit_nbr><subdivision></subdivision><account></account><aor></aor><amount>0</amount><import_ref></import_ref><line_nbr_desc></line_nbr_desc><last_user>DJONE2</last_user></entry_dtl>
</frmJE>

insert into entry_hdr(entry_date, create_user, fiscal_period, fiscal_year, last_user, last_update, last_activity)
SELECT entry_date, nullif(create_user,''), nullif(fiscal_period,''), nullif(fiscal_year,''), nullif(last_user,''), getdate(), 'A'
FROM OPENXML (@idoc, '/frmJE/entry_hdr',2)
WITH ( entry_date datetime 'entry_date',
create_user varchar(15) 'create_user',
fiscal_period varchar(2) 'fiscal_period',
fiscal_year varchar(4) 'fiscal_year',
last_user varchar(15) 'last_user')

Thanks in Advance.

Thanks

Jack

ranganath
Posting Yak Master

209 Posts

Posted - 2009-12-02 : 05:33:14

Hi Try with this

DECLARE @XML XML
SET @XML =

'<frmJE>
<entry_hdr>
<current_row>3</current_row>
<form_action>SAVE</form_action>
<change_ind>Y</change_ind>
<form_name>frmJE</form_name>
<entry_date>10/28/2003</entry_date>
<change_lvl>HDR,DTL</change_lvl>
<entry_id></entry_id>
<create_user></create_user>
<fiscal_period></fiscal_period>
<fiscal_year></fiscal_year>
<last_user>DJONE2</last_user>
</entry_hdr>
<entry_dtl>
<unit_nbr>99002</unit_nbr>
<subdivision>525</subdivision>
<account>6072</account>
<aor>219011</aor>
<amount>1</amount>
<import_ref>1</import_ref>
<line_nbr_desc>1</line_nbr_desc>
<last_user>DJONE2</last_user>
</entry_dtl>
<entry_dtl>
<unit_nbr>99002</unit_nbr>
<subdivision>525</subdivision>
<account>6072</account>
<aor>219011</aor>
<amount>1</amount>
<import_ref>1</import_ref>
<line_nbr_desc>1</line_nbr_desc>
<last_user>DJONE2</last_user>
</entry_dtl>
<entry_dtl>
<unit_nbr></unit_nbr>
<subdivision></subdivision>
<account></account>
<aor></aor>
<amount>0</amount>
<import_ref></import_ref>
<line_nbr_desc></line_nbr_desc>
<last_user>DJONE2</last_user>
</entry_dtl>
<entry_dtl>
<unit_nbr></unit_nbr>
<subdivision></subdivision>
<account></account>
<aor></aor>
<amount>0</amount>
<import_ref></import_ref>
<line_nbr_desc></line_nbr_desc>
<last_user>DJONE2</last_user>
</entry_dtl>
</frmJE>'

SELECT p.n.value('current_row[1]', 'INT') AS 'current_row',
p.n.value('form_action[1]', 'VARCHAR(100)') AS 'form_action',
p.n.value('change_ind[1]', 'VARCHAR(6)') AS 'change_ind',
p.n.value('form_name[1]','Varchar(100)') 'form_name',
p.n.value('entry_date[1]','Varchar(100)') 'entry_date',
p.n.value('change_lvl[1]','Varchar(100)') 'change_lvl',
p.n.value('entry_id[1]','Varchar(100)') 'entry_id',
p.n.value('create_user[1]','Varchar(100)') 'create_user',
p.n.value('fiscal_period[1]','Varchar(100)') 'fiscal_period',
p.n.value('fiscal_year[1]','Varchar(100)') 'fiscal_year',
p.n.value('last_user[1]','Varchar(100)') 'last_user'

FROM @XML.nodes('/frmJE/entry_hdr') AS p(n)
CROSS APPLY n.nodes('/frmJE/entry_hdr') AS t(n)

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-12-02 : 06:31:09

Thanks for your reply...

This is also returning empty values.

After reading these from OPENXML, again i am going to generate XML string. If we have any empty values after reading from OPENXML, it's not going to generate the XML tag for that perticular field.
That's why i want to store NULL values if we have any empty values.

Even this also i am fixing by using NULLIF.. i just want to know is there any other alternative to avoid NULLIF.

Help me guys....

Thanks

Jack

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-12-02 : 23:04:40

Is there anyone to help me out from this...

Urgent

Thanks

Jack

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources