December 13, 2015 at 11:06 pm
Hi All,
I need help declaring the namespace below, what I have doesn't seem to work when retrieving the data,
any help would be appreciated.
The XML without the namespace works perfectly when returning data..
DECLARE @JobInvoicePayload XML =
'<ns2:updateSourcingJobInvoiceRequest xmlns="http://www.tagcmd.com/beans/schema/ebs/jobInvoice" xmlns:ns2="http://www.tagcmd.com/beans/schema/ebs/jobInvoiceService">
<JobInvoiceDtls>
<MUNumber>60040132</MUNumber>
<InvoiceNumber>10000001</InvoiceNumber>
<Currency>GBP</Currency>
<Status>INVOICED</Status>
<BillCustNumber>3040</BillCustNumber>
<BillSiteId>2001</BillSiteId>
<BillAddressId>3074</BillAddressId>
<BillContactId>100001046</BillContactId>
<NetInvoiceAmount>2100</NetInvoiceAmount>
<GrossInvoiceAmount>2520</GrossInvoiceAmount>
<Tax>420</Tax>
<PaymentTerm>60 NET</PaymentTerm>
<CreationDate>2015-12-09+01:00</CreationDate>
<UserId>TCS_SOVIK</UserId>
<JobStatus>INVOICED</JobStatus>
<Jobs>
<JobDetails>
<JobID>5050000003</JobID>
<CustomerPONumber>PO-5050000103</CustomerPONumber>
</JobDetails>
</Jobs>
</JobInvoiceDtls>
</ns2:updateSourcingJobInvoiceRequest>'
;
WITH XMLNAMESPACES ('http://www.tagcmd.com/beans/schema/ebs/jobInvoiceService' AS ns2)
SELECT
@JobInvoicePayload AS JobInvoicePayload,
Jo.JobID,
GETUTCDATE() AS CreatedDate
FROM
--@JobInvoicePayload.nodes('/ns2:updateSourcingJobInvoiceRequest/JobInvoiceDtls/Jobs/JobDetails') AS JobInvoiceFromEBS (J)
@JobInvoicePayload.nodes('/updateSourcingJobInvoiceRequest/JobInvoiceDtls/Jobs/JobDetails') AS JobInvoiceFromEBS (J)
INNER JOIN Sourcing.vJob Jo
ON Jo.JobNumber = J.value('JobID[1]', 'NVARCHAR(64)')
Thanks
December 14, 2015 at 12:44 am
This should get you started
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @JobInvoicePayload XML =
'<ns2:updateSourcingJobInvoiceRequest xmlns="http://www.tagcmd.com/beans/schema/ebs/jobInvoice" xmlns:ns2="http://www.tagcmd.com/beans/schema/ebs/jobInvoiceService">
<JobInvoiceDtls>
<MUNumber>60040132</MUNumber>
<InvoiceNumber>10000001</InvoiceNumber>
<Currency>GBP</Currency>
<Status>INVOICED</Status>
<BillCustNumber>3040</BillCustNumber>
<BillSiteId>2001</BillSiteId>
<BillAddressId>3074</BillAddressId>
<BillContactId>100001046</BillContactId>
<NetInvoiceAmount>2100</NetInvoiceAmount>
<GrossInvoiceAmount>2520</GrossInvoiceAmount>
<Tax>420</Tax>
<PaymentTerm>60 NET</PaymentTerm>
<CreationDate>2015-12-09+01:00</CreationDate>
<UserId>TCS_SOVIK</UserId>
<JobStatus>INVOICED</JobStatus>
<Jobs>
<JobDetails>
<JobID>5050000003</JobID>
<CustomerPONumber>PO-5050000103</CustomerPONumber>
</JobDetails>
</Jobs>
</JobInvoiceDtls>
</ns2:updateSourcingJobInvoiceRequest>;'
;
WITH XMLNAMESPACES ('http://www.tagcmd.com/beans/schema/ebs/jobInvoiceService' AS ns2
,DEFAULT 'http://www.tagcmd.com/beans/schema/ebs/jobInvoice')
SELECT
JobInvoiceFromEBS.J.value('(MUNumber/text())[1]' ,'INT' ) AS MUNumber
,JobInvoiceFromEBS.J.value('(InvoiceNumber/text())[1]' ,'VARCHAR(10)' ) AS InvoiceNumber
,JobInvoiceFromEBS.J.value('(Currency/text())[1]' ,'CHAR(3)' ) AS Currency
,JOB.DETAIL.value('(JobID/text())[1]' ,'BIGINT' ) AS JobID
,JOB.DETAIL.value('(CustomerPONumber/text())[1]' ,'VARCHAR(10)' ) AS CustomerPONumber
FROM @JobInvoicePayload.nodes('/ns2:updateSourcingJobInvoiceRequest/JobInvoiceDtls') AS JobInvoiceFromEBS (J)
CROSS APPLY JobInvoiceFromEBS.J.nodes('Jobs/JobDetails') AS JOB(DETAIL);
Results
MUNumber InvoiceNumber Currency JobID CustomerPONumber
----------- ------------- -------- -------------------- ----------------
60040132 10000001 GBP 5050000003 PO-5050000
December 14, 2015 at 12:59 am
Hi,
This works perfectly, Thanks 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply