March 9, 2015 at 11:06 am
Ok, I'm missing something. I want to take this XML and put it into a table with CustomerId and MatchingSetId. With this SQL, each MatchingSetId gets assigned to each CustomerId instead of retaining the relationships in the XML.
declare @myXML XML = '<CustomerMatchings>
<CustomerRecord CustomerId="10600">
<MatchingSetId>11</MatchingSetId>
<MatchingSetId>13</MatchingSetId>
<MatchingSetId>18</MatchingSetId>
<MatchingSetId>23</MatchingSetId>
<MatchingSetId>24</MatchingSetId>
<MatchingSetId>25</MatchingSetId>
<MatchingSetId>28</MatchingSetId>
<MatchingSetId>29</MatchingSetId>
</CustomerRecord>
<CustomerRecord CustomerId="10700">
<MatchingSetId>47</MatchingSetId>
<MatchingSetId>49</MatchingSetId>
<MatchingSetId>50</MatchingSetId>
<MatchingSetId>53</MatchingSetId>
<MatchingSetId>54</MatchingSetId>
<MatchingSetId>55</MatchingSetId>
<MatchingSetId>57</MatchingSetId>
<MatchingSetId>58</MatchingSetId>
<MatchingSetId>62</MatchingSetId>
<MatchingSetId>63</MatchingSetId>
<MatchingSetId>65</MatchingSetId>
<MatchingSetId>66</MatchingSetId>
</CustomerRecord>
</CustomerMatchings>'
DECLARE @RecvMessages TABLE (id INT IDENTITY(1,1), RecvReqMsg XML)
INSERT INTO @RecvMessages (RecvReqMsg)
SELECT @myXML
;WITH PreShred (CustomerId, MatchSetsXml) AS (
SELECT
r.t.value('@CustomerId', 'bigint') as CustomerId,
m.RecvReqMsg.query('/CustomerMatchings/CustomerRecord/MatchingSetId') AS MatchSetsXml
FROM
@RecvMessages m
CROSS APPLY
m.RecvReqMsg.nodes('/CustomerMatchings/CustomerRecord') AS r(t)
)
SELECT
CustomerId,
Shred.MatchingSetId.value('.', 'bigint') AS MatchSetId
FROM
PreShred
CROSS APPLY
MatchSetsXml.nodes('/MatchingSetId') AS Shred(MatchingSetId)
March 9, 2015 at 11:32 am
Try "joining" the two. Easiest way is to cascade the cross apply statements, i.e.
SELECT
r.t.value('(.[1])/@CustomerId', 'bigint') as CustomerId,
b.value('.[1]','varchar(2000)')
FROM
@RecvMessages m
CROSS APPLY
m.RecvReqMsg.nodes('/CustomerMatchings/CustomerRecord') AS r(t)
CROSS APPLY
t.nodes('MatchingSetId') as a(b) --Notice that this is based on the node statement in the previous CROSS APPLY.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 9, 2015 at 11:36 am
Ah, interesting. I found another way to do it in the interim, but I think I like your approach better. Thanks!
March 9, 2015 at 12:46 pm
Quick suggestion, slightly more efficient at least for this data set
😎
USE tempdb;
GO
declare @myXML XML = '<CustomerMatchings>
<CustomerRecord CustomerId="10600">
<MatchingSetId>11</MatchingSetId>
<MatchingSetId>13</MatchingSetId>
<MatchingSetId>18</MatchingSetId>
<MatchingSetId>23</MatchingSetId>
<MatchingSetId>24</MatchingSetId>
<MatchingSetId>25</MatchingSetId>
<MatchingSetId>28</MatchingSetId>
<MatchingSetId>29</MatchingSetId>
</CustomerRecord>
<CustomerRecord CustomerId="10700">
<MatchingSetId>47</MatchingSetId>
<MatchingSetId>49</MatchingSetId>
<MatchingSetId>50</MatchingSetId>
<MatchingSetId>53</MatchingSetId>
<MatchingSetId>54</MatchingSetId>
<MatchingSetId>55</MatchingSetId>
<MatchingSetId>57</MatchingSetId>
<MatchingSetId>58</MatchingSetId>
<MatchingSetId>62</MatchingSetId>
<MatchingSetId>63</MatchingSetId>
<MatchingSetId>65</MatchingSetId>
<MatchingSetId>66</MatchingSetId>
</CustomerRecord>
</CustomerMatchings>'
DECLARE @RecvMessages TABLE (id INT IDENTITY(1,1), RecvReqMsg XML)
INSERT INTO @RecvMessages (RecvReqMsg)
SELECT @myXML;
SELECT
RM.id
,CR.DATA.value('@CustomerId','INT') AS CustomerId
,MS.DATA.value('.[1]','INT') AS MatchingSetId
FROM @RecvMessages RM
CROSS APPLY RM.RecvReqMsg.nodes('CustomerMatchings/CustomerRecord') AS CR(DATA)
CROSS APPLY CR.DATA.nodes('MatchingSetId') AS MS(DATA)
;
Results
id CustomerId MatchingSetId
----------- ----------- -------------
1 10600 11
1 10600 13
1 10600 18
1 10600 23
1 10600 24
1 10600 25
1 10600 28
1 10600 29
1 10700 47
1 10700 49
1 10700 50
1 10700 53
1 10700 54
1 10700 55
1 10700 57
1 10700 58
1 10700 62
1 10700 63
1 10700 65
1 10700 66
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply