query to update a table with XML value

Question

Post reply

query to update a table with XML value

fafful

SSC Eights!

Points: 810
More actions
August 10, 2016 at 6:03 am

#400552

I have 2 tables:
Table_A (InsuranceNo, JobNo,Processed) values('Anton1',NULL),('Jason1', NULL),('Citirus2',NULL)
Table_B (InsuranceNo, XmlResponse)
values ('Anton1','<Boot><Jobs><Job_entered jobno="2122" logged_info="Job number 2122">2122</Job_entered></Jobs></Boot>'),
('Citirus2','<Boot><ErrorDetails> a problem occured with data </ErrorDetails></Boot>')
Question:
I want to update Table_A Jobno column with the Jobno value from its own xml column in Table_B and Processed column to 'YES' when both tables are joined on InsuranceNo columns. Where there is an XML error no update for Jobno in Table_A, but update for Processed column in Table_A with 'NO'
I wrote this but it does not update:
Update B
set B.Processed =
case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=1)
Then 'NO'
else 'YES'
End,
B.Jobno =
case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=0)
Then
T.[XMLResponse].value('(/Boot/Jobs/Job_entered/@jobno)[1]','varchar(255)')
End
from Table_B T inner join Table_A B on (T.InsuranceNo=B.InsuranceNo)
Thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

while working with xml, please make sure you are using the exact same name, because XML is Case - Sensitive.

The problem is you need to change the "jobs" into "Jobs".

you just need to do the following:

Previous code:

T.[xmlresponse].value('(/Boot/jobs/Job_entered/jobno)[1]','varchar(255)')

just change it to

T.[xmlresponse].value('(/Boot/Jobs/Job_entered/@jobno)[1]','varchar(255)')

hope it helps.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

fafful (8/10/2016)
I have 2 tables:
Table_A (InsuranceNo, JobNo,Processed) values('Anton1',NULL),('Jason1', NULL),('Citirus2',NULL)
Table_B (InsuranceNo, XmlResponse)
values ('Anton1','<Boot><Jobs><Job_entered jobno="2122" logged_info="Job number 2122">2122</Job_entered></Jobs></Boot>'),
('Citirus2','<Boot><ErrorDetails> a problem occured with data </ErrorDetails></Boot>')
Question:
I want to update Table_A Jobno column with the Jobno value from its own xml column in Table_B and Processed column to 'YES' when both tables are joined on InsuranceNo columns. Where there is an XML error no update for Jobno in Table_A, but update for Processed column in Table_A with 'NO'
I wrote this but it does not update:
Update B
set B.Processed =
case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=1)
Then 'NO'
else 'YES'
End,
B.Jobno =
case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=0)
Then
T.[xmlresponse].value('(/Boot/jobs/Job_entered/jobno)[1]','varchar(255)')
End
from Table_B T inner join Table_A B on (T.InsuranceNo=B.InsuranceNo)
Thanks in advance

This is the wrong way to do it. By definition, XML is a gross violation of normalization and doesn't even meet 1st Normal Form. Shred the XML on receipt and store the resulting data in a properly normalized table. If you then need to produce XML to send as a file or provide to a GUI, do that on demand but don't store XML like this in a table except a staging table to shred it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

fafful SSC Eights! Points: 810 More actions · Answer 3

Though it did not solve the problem but it helps. This was a typo from me.

The issue is i am trying to update a couple of rows in a join statement and the value for the update must come from an XML stored in Table_B for the row that the pointer is at.

drew.allen SSC Guru Points: 76985 More actions · Answer 4

There were actually two problems. twin.devil corrected both, but only mentioned one. The second problem is that jobno is an attribute, so you need to use "@" to indicate you want an attribute instead of an element.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

fafful SSC Eights! Points: 810 More actions · Answer 5

Thanks. The Table_B with the XML is a Temporary table that has the results of some data processing; so is a form of a staging table.

The idea is that after processing the temporay table TABLE_B has the xml form all the insurance numbers that were processed and i need to shred and get just the job number and update the Table_A with the value.

fafful SSC Eights! Points: 810 More actions · Answer 6

Thanks. I made a change to the syntax and the Jobno was not retrieved:

Update B

set B.[Processed] =

case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=1)

Then 'YES'

else 'NO'

End,

TB.[Jobno] =

case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=0)

Then T.[XMLResponse].value('(/Boot/Jobs/Job_entered/@Jobno[1]','varchar(255)')

End

from Table_B T inner join Table_A B on (B.InsuranceNo=T.InsuranceNo)

drew.allen SSC Guru Points: 76985 More actions · Answer 7

fafful (8/10/2016)
Thanks. I made a change to the syntax and the Jobno was not retrieved:
Update B
set B.[Processed] =
case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=1)
Then 'YES'
else 'NO'
End,
TB.[Jobno] =
case when (T.[XMLResponse].exist('/Boot/ErrorDetails')=0)
Then T.[XMLResponse].value('(/Boot/Jobs/Job_entered/@Jobno[1]','varchar(255)')
End
from Table_B T inner join Table_A B on (B.InsuranceNo=T.InsuranceNo)

Your cases still don't match. (And you're missing a paren.)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

fafful SSC Eights! Points: 810 More actions · Answer 8

If is about Processed = Yes or No in the first case statement then OK but has no bearing on the technique, right?

I cannot see where the missing Parenthesis is?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

You'd have none of these problems if you stored the data as normalized data instead of XML. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

Jeff Moden (8/10/2016)
You'd have none of these problems if you stored the data as normalized data instead of XML. 😉

🙂

Umer Akram SSC-Insane Points: 22209 More actions · Answer 11

drew.allen (8/10/2016)
There were actually two problems. twin.devil corrected both, but only mentioned one. The second problem is that jobno is an attribute, so you need to use "@" to indicate you want an attribute instead of an element.
Drew

Thanks Drew, Its funny that i completely missed that one in explanation not in the code 😀