Parsing XML Data - ParentChildren
-
I’ll try to be as clear as possible with the initial details:
1. I have a view (V_DATA) that is returning a set of rows (500+ rows)
2. Each row has a unique audit_id
3. There is a column called 'parameters' which has XML data stored as nvarchar(max). So i casted it as XML like: CAST(PARAMETER AS XML) AS PARAMETER to convert to XML.
4. Each XML structure may be different because there will be a difference in parent nodes (i.e. 1 xml may have 3 <detail> nodes while another may only have 1 <detail> node). However, the names will be the same for all the nodes for each xml structure.
5. The field <ID> has a value of -1 which links it's data to the other <ID> fields only in that structure.
Sample XML Structure per row:
<SNIP>
----------------------------------------------------
What i need to achieve:
I need to parse the XML in each row returned and print the data in table format. The data returned could include nulls and repeated data (based on parent nodes available).
Desired Table Output:
<snip>
Current Table Output:
<snip>
What have i done so far:
Currently i am returning all rows and parsed the xml in the structure for the most part.
Problem:
I can't print the repeated parent nodes because i am using C.C.value('()[1]', 'nvarchar(max)') which i believe would only bring back 1 parent only. I tried using UNION to get the other parents but i am not still not getting all of them.
If my logic is wrong in parsing this kind of structure please let me know how to approach this.
Thanks in advanced.
Sincerely,
Leon CLF
DB Administrator -
You haven't given your desired output, but it looks like you can get what you want by simply using the nodes() function on the subtrees received from the first nodes() function.
DECLARE @x XML = '
<REGISTRATION.1>
<REGISTRATION>
<ID>-1</ID>
<TYPE>Both</TYPE>
<CASH>N</CASH>
<SEC>N</SEC>
<BO_REF>1233048</BO_REF>
<TFR>N</TFR>
<HOLD_REF>2550689</HOLD_REF>
<WAIT>N</WAIT>
<P_TYPE>P</P_TYPE>
<P_APPROP>N</P_APPROP>
<P_CLEARANCE>N</P_CLEARANCE>
<P_VALUE>130685.3</P_VALUE>
</REGISTRATION>
<REGISTRATION_DETAILS>
<ID>-1</ID>
<ISIN_CODE>II03589258655</ISIN_CODE>
<QTY>1418</QTY>
<PRICE>67.25</PRICE>
</REGISTRATION_DETAILS>
<REGISTRATION_DETAILS>
<ID>-1</ID>
<ISIN_CODE>BB078958655</ISIN_CODE>
<QTY>18</QTY>
<PRICE>69.5</PRICE>
</REGISTRATION_DETAILS>
<NAME_DETAILS>
<NAME>Bob</NAME>
<OTHER>BBDDDD58655</OTHER>
<AGE>18</AGE>
</NAME_DETAILS>
<NAME_DETAILS>
<NAME>PAM</NAME>
<OTHER>BDWDWD8655</OTHER>
<AGE>19</AGE>
</NAME_DETAILS>
</REGISTRATION.1>
'
Select
C.C.value('(/REGISTRATION.1/REGISTRATION/ID)[1]', 'nvarchar(max)') as ID
,C.C.value('(/REGISTRATION.1/REGISTRATION/TYPE)[1]', 'nvarchar(max)') as TYPE
,C.C.value('(/REGISTRATION.1/REGISTRATION/CASH)[1]', 'nvarchar(max)') as CASH
,C.C.value('(/REGISTRATION.1/REGISTRATION/SEC)[1]', 'nvarchar(max)') as SEC
,C.C.value('(/REGISTRATION.1/REGISTRATION/BO_REF)[1]', 'int') as BO_REF
,C.C.value('(/REGISTRATION.1/REGISTRATION/TFR)[1]', 'nvarchar(max)') as TFR
,C.C.value('(/REGISTRATION.1/REGISTRATION/AC_HOLD_REF)[1]', 'int') as HOLD_REF
,C.C.value('(/REGISTRATION.1/REGISTRATION/WAIT)[1]', 'nvarchar(max)') as WAIT
,C.C.value('(/REGISTRATION.1/REGISTRATION/P_TYPE)[1]', 'nvarchar(max)') as PTYPE
,C.C.value('(/REGISTRATION.1/REGISTRATION/P_APPROP)[1]', 'nvarchar(max)') as P_APPROP
,C.C.value('(/REGISTRATION.1/REGISTRATION/P_CLEARANCE)[1]', 'nvarchar(max)') as P_CLEARANCE
,C.C.value('(/REGISTRATION.1/REGISTRATION/P_VALUE)[1]', '[numeric](28, 13)') as P_VALUE
--Retrieve other parents
,c1.c1.value('(ID)[1]', 'nvarchar(max)') as ID
,c1.c1.value('(ISIN_CODE)[1]', 'nvarchar(max)') as ISIN
,c1.c1.value('(QTY)[1]', '[numeric](28, 6)') as QUANTITY
,c1.c1.value('(PRICE)[1]', '[numeric](28, 6)') as PRICE
from @x.nodes('/REGISTRATION.1') as C(C)
CROSS APPLY c.c.nodes('REGISTRATION_DETAILS') c1(c1)
You need to make sure that all subsequent instances use a relative path rather than an absolute path.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (5/16/2016)
You haven't given your desired output, but it looks like you can get what you want by simply using the nodes() function on the subtrees received from the first nodes() function.<snip code>
Hello Drew,
Thanks for the reply, i updated the original post with the desired output but what you posted here i can work with that output.
Two questions:
1. How can i tweak your code to use the view, V_DATA (this is where i am getting the XML data from)? V_DATA is the view that is returning the xml rows.
::
from V_DATA as p
CROSS APPLY p.PARAMETER.nodes('/REGISTRATION.1') as C(C)
CROSS APPLY C.C.nodes('REGISTRATION_DETAILS') c1(c1)
?
2. How can i tweak it further to print the NAME_DETAILS information? Do i need to add additional cross apply's for other parents?
::
CROSS APPLY c1.c1.nodes('NAME_DETAILS ') c2(c2)
CROSS APPLY c2.c2.nodes('OTHER_POSSIBLE_PARENTS') c3(c3)
Sincerely,
Leon CLF
DB Administrator -
leon_clf (5/16/2016)
drew.allen (5/16/2016)
You haven't given your desired output, but it looks like you can get what you want by simply using the nodes() function on the subtrees received from the first nodes() function.<snip code>
Hello Drew,
Thanks for the reply, i updated the original post with the desired output but what you posted here i can work with that output.
Two questions:
1. How can i tweak your code to use the view, V_DATA (this is where i am getting the XML data from)? V_DATA is the view that is returning the xml rows.
::
from V_DATA as p
CROSS APPLY p.PARAMETER.nodes('/REGISTRATION.1') as C(C)
CROSS APPLY C.C.nodes('REGISTRATION_DETAILS') c1(c1)
?
2. How can i tweak it further to print the NAME_DETAILS information? Do i need to add additional cross apply's for other parents?
::
CROSS APPLY c1.c1.nodes('NAME_DETAILS ') c2(c2)
CROSS APPLY c2.c2.nodes('OTHER_POSSIBLE_PARENTS') c3(c3)
c represents each REGISTRATION.1 subtree; c1 represents each REGISTRATION.1/REGISTRATION_DETAILS subtree. So, is NAME_DETAILS a child of REGISTRATION.1 or REGISTRATION.1/REGISTRATION_DETAILS?
Drew
PS: It might help if you used more mnemonic aliases, e.g., r1 for REGISTRATION.1 and rd for REGISTRATION_DETAILS.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (5/16/2016)
leon_clf (5/16/2016)
drew.allen (5/16/2016)
You haven't given your desired output, but it looks like you can get what you want by simply using the nodes() function on the subtrees received from the first nodes() function.<snip code>
Hello Drew,
Thanks for the reply, i updated the original post with the desired output but what you posted here i can work with that output.
Two questions:
1. How can i tweak your code to use the view, V_DATA (this is where i am getting the XML data from)? V_DATA is the view that is returning the xml rows.
::
from V_DATA as p
CROSS APPLY p.PARAMETER.nodes('/REGISTRATION.1') as C(C)
CROSS APPLY C.C.nodes('REGISTRATION_DETAILS') c1(c1)
?
2. How can i tweak it further to print the NAME_DETAILS information? Do i need to add additional cross apply's for other parents?
::
CROSS APPLY c1.c1.nodes('NAME_DETAILS ') c2(c2)
CROSS APPLY c2.c2.nodes('OTHER_POSSIBLE_PARENTS') c3(c3)
c represents each REGISTRATION.1 subtree; c1 represents each REGISTRATION.1/REGISTRATION_DETAILS subtree. So, is NAME_DETAILS a child of REGISTRATION.1 or REGISTRATION.1/REGISTRATION_DETAILS?
Drew
PS: It might help if you used more mnemonic aliases, e.g., r1 for REGISTRATION.1 and rd for REGISTRATION_DETAILS.
I agree mnemonic aliases should be used.
Yes, NAME_DETAILS is a child of REGISTRATION.1
Sincerely,
Leon CLF
DB Administrator -
drew.allen (5/16/2016)
leon_clf (5/16/2016)
drew.allen (5/16/2016)
<snip>Hi Drew,
I came across a little problem i noticed that data is being lost with each CROSS APPLY i add, i did something like:
::
:
from V_DATA
CROSS APPLY V_DATA.PARAMETER.nodes('/HAM.5') H1(H1)
CROSS APPLY H1.H1.nodes('ACC') A(A)
CROSS APPLY H1.H1.nodes('NAME') N(N)
CROSS APPLY H1.H1.nodes('MAPLE') M(M)
CROSS APPLY H1.H1.nodes('USER') U(U)
CROSS APPLY H1.H1.nodes('NATIONALITY') NI(NI)
Each node may or may not exist in the XML data returned from V_DATA. All i wanted it to do was iterate through the nodes that repeat.
So like one XML could be returned as:
<HAM.5><ACC>1234</ACC>
<NAME>BOB</NAME>
<MAPLE>T</MAPLE>
<MAPLE>H</MAPLE>
<MAPLE>I</MAPLE>
<MAPLE>U</MAPLE>
<USER>B</USER>
<USER>K</USER>
<NATIONALITY>CA</NATIONALITY>
</HAM.5>
and another could be:
<HAM.5><ACC>4444</ACC>
<NAME>JOE</NAME>
<USER>J</USER>
<USER>K</USER>
</HAM.5>
Output:
Sincerely,
Leon CLF
DB Administrator -
You still haven't provided expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (5/17/2016)
You still haven't provided expected results.Drew
Sorry about that, i updated the previous post with the desired output for that specific XML in the post.
Thanks!
Sincerely,
Leon CLF
DB Administrator -
Two things:
1) You said that you were missing data? What results are you getting?
2) You xml data doesn't support the desired results. Your desired results are treating siblings as if they were ancestor/descendants. By default, the order of elements within a document is irrelevant, so the way to enforce a relationship is through an ancestor/descendant relationship, but you are treating it as if the order is relevant, and expecting to match siblings based on order. The following two are equivalent and should return the same results:
<HAM.5>
<ACC>1234</ACC>
<NAME>BOB</NAME>
<MAPLE>T</MAPLE>
<MAPLE>H</MAPLE>
<MAPLE>I</MAPLE>
<MAPLE>U</MAPLE>
<USER>B</USER>
<USER>K</USER>
<NATIONALITY>CA</NATIONALITY>
</HAM.5>
<HAM.5>
<ACC>1234</ACC>
<NAME>BOB</NAME>
<MAPLE>H</MAPLE>
<MAPLE>I</MAPLE>
<MAPLE>T</MAPLE>
<MAPLE>U</MAPLE>
<USER>K</USER>
<USER>B</USER>
<NATIONALITY>CA</NATIONALITY>
</HAM.5>
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (5/17/2016)
Two things:1) You said that you were missing data? What results are you getting?
2) You xml data doesn't support the desired results. Your desired results are treating siblings as if they were ancestor/descendants. By default, the order of elements within a document is irrelevant, so the way to enforce a relationship is through an ancestor/descendant relationship, but you are treating it as if the order is relevant, and expecting to match siblings based on order. The following two are equivalent and should return the same results:
Drew
The thing is the structure of the XMLs i'm dealing with isn't identical. What i want is all the information stored in one table no matter if nulls fill the spaces.
How can i do that with the XML data being retrieved below?
Each <HAM.5> has a unique AUDIT_ID so assume we can repeat that per row associated with the data so we can tell which <HAM.5> data is which.
<HAM.5>
<ACC>1234</ACC>
<NAME>
<NAME_PERSON>BOB</NAME_PERSON>
<NAME_ADDRESS>#88 SOMEWHERE</NAME_ADDRESS>
</NAME>
<MAPLE>T</MAPLE>
<MAPLE>H</MAPLE>
<MAPLE>I</MAPLE>
<MAPLE>U</MAPLE>
<USER>B</USER>
<USER>K</USER>
<NATIONALITY>CA</NATIONALITY>
</HAM.5>
<HAM.5>
<ACC>4444</ACC>
<NAME>JOE</NAME>
<USER>J</USER>
<USER>K</USER>
</HAM.5>
<HAM.5>
<ACC>1234</ACC>
<NAME>
<NAME_PERSON>BOB</NAME_PERSON>
<NAME_ADDRESS>#88 SOMEWHERE</NAME_ADDRESS>
</NAME>
<MAPLE>T</MAPLE>
<MAPLE>H</MAPLE>
<MAPLE>I</MAPLE>
<MAPLE>U</MAPLE>
<USER>B</USER>
<USER>K</USER>
<NATIONALITY>CA</NATIONALITY>
</HAM.5>
The output should contain all the information but i don't know what it should look like.
Sincerely,
Leon CLF
DB Administrator -
Try changing your CROSS APPLY to an OUTER APPLY. The CROSS APPLY filters out nodes that don't have any instances of the element specified, whereas the OUTER APPLY will leave the nodes that don't have them.
If that doesn't fix your problem, then show us the results that you are getting. It really helps to troubleshoot if we know how the expected results differ from the actual results. You have only ever supplied the expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen (5/17/2016)
Try changing your CROSS APPLY to an OUTER APPLY. The CROSS APPLY filters out nodes that don't have any instances of the element specified, whereas the OUTER APPLY will leave the nodes that don't have them.If that doesn't fix your problem, then show us the results that you are getting. It really helps to troubleshoot if we know how the expected results differ from the actual results. You have only ever supplied the expected results.
Drew
Hey the outer apply is giving the information i believe i need but i'm still verifying the output.
Sorry about not supplying actual results for i am dealing with sensitive information. And i would need to edit the data returned with dummy information + XML changes.
Sincerely,
Leon CLF
DB Administrator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply