September 17, 2009 at 7:42 am
I'm trying to extract information from an xml document and set up a table to store a few child segments. I'm using the following code, but when I run a select against the table, there is no data. I've tried everything but I still get the same result. I've changed some of the names to protect the innocent....also the document is bigger than the example I'm giving because it would take too long to change names all down the line. Still carps out, though.
use [dbname]
go
if not OBJECT_ID('dbo.m_Loan') is null
drop table dbo.m_Loan;
go
create table dbo.m_Loan(
XTLID int not null primary key,
ServicerName nvarchar(50) not null,
LoanNumber nvarchar(25) not null);
go
set nocount on
declare @x xml;
select @x = bulkcolumn from openRowSet(bulk 'c:\mik_Test\MinnieDownload.txt',single_blob) as b;
with b2(XTLID,ServicerName,LoanNumber) as (
SELECT tab.col.value('@XTLID','nvarchar(50)') AS 'XTLID',
tab.col.value('@ServicerName','nvarchar(50)') AS 'ServicerName',
tab.col.value('@LoanNumber','nvarchar(25)') AS 'LoanNumber'
from @x.nodes('m_Loan')AS tab(col))
merge into dbo.m_Loan as b1
using b2
on(b1.XTLID = b2.XTLID)
when matched then
update set
b1.ServicerName = b2.ServicerName
, b1.LoanNumber = b2.LoanNumber
when not matched then
insert(XTLID,ServicerName,LoanNumber)
values(b2.XTLID, b2.ServicerName, b2.LoanNumber);
go
select * from m_Loan
xml:
0001-01-01T00:00:00.0000000-05:00
AcknowledgeReceipt
Dan Cannon
at-koze
289
EmailAddress
314-961-9587
TelephoneNumber
423
Received
Download Successful
358946
Wally
0007852049
358946
Insurer Info
Insurer_Code
Other
358946
Insurer Info
Insurer_Loan_Number
358946
Investor Info
Client_Investor_Action_Name
360499
Daisy
109999550
360499
Insurer Info
Insurer_Code
Other
360499
Insurer Info
Insurer_Loan_Number
0000000000000
I would greatly appreciate a little advice since I've been trying everything. :crying:
September 17, 2009 at 10:53 am
Hi, a couple things to notice:
XTLID, ServicerName, and LoanNumber are elements, no attributes. Therefore, you cannot use @XTLID.
You cannot jump right in the middle of an xml structure with the beginning node. It needs to be fully qualified: Change @x.nodes('m_Loan') to @x.nodes('MinnieDownload/MinnieDownloadItem/m_Loan').
Last but not least: Since you're using an xml file with namespace definition you have to declare it in your statement as well.
Note: Since XML language is case sensitive you need to make sure the elements used in the query do match your xml file structure.
The modified XQuery would look like
;WITH XMLNAMESPACES (
DEFAULT 'HTTP://Pluto.miksOnline.Com/mik/Vendor/MinnieDownloadWSE.xsd'
)
SELECT tab.col.value('XTLID[1]','nvarchar(50)') AS 'XTLID',
tab.col.value('ServicerName[1]','nvarchar(50)') AS 'ServicerName',
tab.col.value('LoanNumber[1]','nvarchar(25)') AS 'LoanNumber'
from @x.nodes('MinnieDownload/MinnieDownloadItem/m_Loan')AS tab(col)
/* result set
XTLIDServicerNameLoanNumber
358946Wally0007852049
360499Daisy109999550
*/
September 17, 2009 at 11:22 am
Thanks! It sort of worked except for the fact that the real web address for the .xsd is down with the flu bug. Or something like that. Gives me hope for the future and my job!:w00t:
September 17, 2009 at 11:57 am
Just one more thing - I was trying to get the select statement to turn into an insert statement and by adding "Distinct" (and the bug mysteriously disappearing from the nasty website), I got data in!
Bless you!:w00t:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply