declare @x xml = '<XML> <UNSTRING STATEMENTNUMBER="48515">
<STRING>CRD001M-OPTIONI</STRING>
<DELIMITERS>
<DELIMITER>aaa</DELIMITER>
</DELIMITERS>
<SUBSTRINGS>WS-MENU-STACK1 WS-MENU-STACK2 WS-MENU-STACK3 WS-MENU-STACK4 WS-MENU-STACK5 WS-MENU-STACK6 WS-MENU-STACK7 WS-MENU-STACK8 WS-MENU-STACK9 WS-MENU-STACK10 TALLYING IN WS-MENU-STACK-TALLY</SUBSTRINGS>
<DELIMITERSFOUND>
<DELIMITER>
</DELIMITER>
</DELIMITERSFOUND>
<INDICES>
<INDEX>
</INDEX>
</INDICES>
<POINTER>
</POINTER>
<TALLYING>
</TALLYING>
<ONOVERFLOW>
</ONOVERFLOW>
</UNSTRING></XML>'
In above XML tag have value which are septate by space I am trying to make below string as below
declare @x xml = '<XML> <UNSTRING STATEMENTNUMBER="48515">
<STRING>CRD001M-OPTIONI</STRING>
<DELIMITERS>
<DELIMITER>aaa</DELIMITER>
</DELIMITERS>
<SUBSTRINGS>WS-MENU-STACK1</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK2</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK3</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK4</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK5</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK6</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK7</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK8</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK9</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK10</SUBSTRINGS>
<SUBSTRINGS>TALLYING</SUBSTRINGS>
<SUBSTRINGS>IN</SUBSTRINGS>
<SUBSTRINGS>WS-MENU-STACK-TALLY</SUBSTRINGS>
<DELIMITERSFOUND>
<DELIMITER>
</DELIMITER>
</DELIMITERSFOUND>
<INDICES>
<INDEX>
</INDEX>
</INDICES>
<POINTER>
</POINTER>
<TALLYING>
</TALLYING>
<ONOVERFLOW>
</ONOVERFLOW>
</UNSTRING></XML>'
Please help here how can I solve is issue by replace or any other thing I have used replace function but not able to find values here <substrings> tag values are septate by space, So find each value have between <substrings> value </substrings>
No comments:
Post a Comment