Parsing XML content with absent elements in SQL Server 2012 -


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


Comments