I need to parse the XML customers in the transaction table for each day. The XML file is coming from external service which is not controlled by me.
The problem occurs when a customer does not make transactions for the day, I can not see it in my table. How do I see that the customer has zero transactions?
Announce @ xml = '& lt; Root & gt; & Lt; Customers & gt; & Lt; Customer ID = "777" & gt; & Lt; Order & gt; & Lt; Order Currency = "USD" id = "888" date = "2014-06-18" & gt; & Lt; Transactions & gt; & Lt; Transaction ID = "998" & gt; & Lt; Date & gt; 2014-08-01 & lt; / Date & gt; & Lt; Itemid & gt; 10001 & lt; / Itemid & gt; & Lt; Amount & gt; 745.96 & lt; / Zodiac & gt; & Lt; / Transactions & gt; & Lt; / Transactions & gt; & Lt; / Order & gt; & Lt; / Order & gt; & Lt; / Customer & gt; & Lt; Customer ID = "778" & gt; & Lt; Order & gt; & Lt; Order id = "999" /> & Lt; / Order & gt; & Lt; / Customer & gt; & Lt; / Customers & gt; & Lt; / Root & gt; '
My Transformation query is like this:
select newid () id, ltrim (rtrim (BCvalue (' @ id ',' nvarchar ( 50) '))) CUSTOMER_ID, ltrim (rtrim (KCvalue (' @ id ',' nvarchar (450) '))) ACCOUNT_ID, ltrim (rtrim (KCvalue (' @ date ',' datetime ')) DATE_PLACED, Ltrim ( Rtrim ('kcvalue' ('currency', 'navratra (50)')) currency, litit (rrtim (tcvu ('iid', 'enchantment (50)')) transaction_id, (TCvalue ('date Transaction_itid, ltrim (rtrim (TCvalue ('zodiac [1]'), TRANSACTION_DATE, ltrim (rtrim (TCvalue ('itemid [1]', 'nvarchar (50)'))), 'Money'))) @ xml.nodes ('/ root / c Externally in the form of K (C) in the form of external BCNode ('/ root / customer / customer / order / order') as TRANSACTION_BANK_CODE B (C) from 'Rahak / Client') (as in '/ root / customer / Ltrim (rtrim (bcvalue ('id', 'nva alert' (50) '))) =) ltrim (rtrim (kcvalue ('. ./../@ id ',' Narature (50) ')) and ((TCVAV (' ../../@ ',' Narature (50) ') is not empty and ltrim (rtrim (kcvalue) ('@ Id', 'narretar (50)')))) = ltrim (rtrim (tcvalue ('../../ @ID', 'nerve (50)')))) or ( Itrim (rtrim (tcvalue ( '../../@ ID' Nrwcr (50))))) is null))
Before you thank you!
You should not apply against cross with full xpath starting with it and where do you Can remove CUSTOMER_ID, 'KCvalue' ('' id ',' nvarchar (50) '), as the BC (' id ',' select newid '),
Result
ID CUSTOMER_ID ACCOUNT_ID TRANSACTION_ID -------------------- --------- ------- ----------- ---------- ------------- - 767 FAA 17-578A-495 E-9EEFA-75E3509B2BD2-7 77 888 998 59 9 65290-EB 7C-429B-AA5F-97EEDEBE 35BD778999Null
Comments
Post a Comment