XML Nodes to retreive the child and its list of values.

Question

XML Nodes to retreive the child and its list of values.

pawan.clong10

SSC Enthusiast

Points: 136
More actions
April 8, 2015 at 10:09 am

#398296

HI all,
i was trying to use the XML read functionality using t-SQL for XML attached
The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.
I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.
Please let me know how can i acheive the tokenNames with its values with the SQL query.
For the reference, i am attaching both SQL script which i am using and the XML entity.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61152 More actions · Answer 1

Do you have some DDL (the create table statement) for ETL.STG_LETTER_RQST?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 2

Yeah please find it below. note that the entry of the XML is the same which was attached.

create table etl.STG_LETTER_RQST

(

dcmnt_mppng_data_txt nVARCHAR(max)

)

insert into etl.STG_LETTER_RQST

select N'<?xml version="1.0" encoding="UTF-8"?> <template-data-map xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0"><entity-descriptor entity-id="22" entity-type="CONSUMER"/><recipient-entity-descriptor entity-id="22" entity-type="CONSUMER"><staging-udp>false</staging-udp></recipient-entity-descriptor><mapping-set-id>6</mapping-set-id><token-value-entry token-name="RECIPIENT_ADDR3"><token-value><value>?????????? ??????????</value></token-value></token-value-entry><token-value-entry token-name="RECIPIENT_ADDR2"><token-value><value>?.????????</value></token-value></token-value-entry><token-value-entry token-name="CNSMR_ACCNTS"><token-value><list-of-values><entity-descriptor entity-id="26" entity-type="CONSUMERACCOUNT"/><recipient-entity-descriptor entity-id="22" entity-type="CONSUMER"><staging-udp>false</staging-udp></recipient-entity-descriptor><mapping-set-id>6</mapping-set-id><token-value-entry token-name="INTEREST_RATES"><token-value><list-of-values><entity-descriptor entity-id="1" entity-type="USER_DEFINED"/><recipient-entity-descriptor entity-id="22" entity-type="CONSUMER"><staging-udp>false</staging-udp></recipient-entity-descriptor><mapping-set-id>6</mapping-set-id><token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT1"><token-value><value>0.00</value></token-value></token-value-entry><token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT7"><token-value><value>191.00</value></token-value></token-value-entry><token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT5"><token-value><value>101.00</value></token-value></token-value-entry></list-of-values></token-value></token-value-entry><token-value-entry token-name="FIN_INFO2"><token-value><list-of-values><entity-descriptor entity-id="1" entity-type="USER_DEFINED"/><recipient-entity-descriptor entity-id="22" entity-type="CONSUMER"><staging-udp>false</staging-udp></recipient-entity-descriptor><mapping-set-id>6</mapping-set-id><token-value-entry token-name="U_FINANCIAL_INFO2_TOT_UNPAID_INT"><token-value><value>1200.00</value></token-value></token-value-entry><token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIMIT9"><token-value><value>0.00</value></token-value></token-value-entry><token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIM10"><token-value><value>0.00</value></token-value></token-value-entry><token-value-entry token-name="U_FINANCIAL_INFO2_TOT_OUTS_UNEARN"><token-value><value>1200.00</value></token-value></token-value-entry></list-of-values></token-value></token-value-entry><token-value-entry token-name="ACCNT_INFO"><token-value><list-of-values><entity-descriptor entity-id="10" entity-type="USER_DEFINED"/><recipient-entity-descriptor entity-id="22" entity-type="CONSUMER"><staging-udp>false</staging-udp></recipient-entity-descriptor><mapping-set-id>6</mapping-set-id><token-value-entry token-name="U_ACCOUNT_NO_OPEN_DATE"><token-value><value>04/07/2015</value></token-value></token-value-entry><token-value-entry token-name="U_ACCOUNT_INFO_DPD"><token-value><value>10</value></token-value></token-value-entry><token-value-entry token-name="U_ACCOUNT_NO_ACCT_NUM"><token-value><value>00027066394534</value></token-value></token-value-entry><token-value-entry token-name="U_ACCOUNT_INFO_ACCT_NAME"><token-value><value>Adnan khalid</value></token-value></token-value-entry></list-of-values></token-value></token-value-entry></list-of-values></token-value></token-value-entry><token-value-entry token-name="RECIPIENT_ADDR1"><token-value><value>89/484</value></token-value></token-value-entry></template-data-map>'

Alan Burstein SSC Guru Points: 61152 More actions · Answer 3

Ok, never mind the DDL; From your XML that you posted I have a temp variable to mimic what you have.

We'll use this sample data:

DECLARE @xml xml =

'<?xml version="1.0" encoding="UTF-8"?>

<template-data-map xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0">

<entity-descriptor entity-id="22" entity-type="CONSUMER"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="RECIPIENT_ADDR3">

<token-value>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR2">

<token-value>

<value>à¸–.à¸ªà¸¸à¸‚à¸¸à¸¡à¸§à¸´à¸—</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="CNSMR_ACCNTS">

<token-value>

<list-of-values>

<entity-descriptor entity-id="26" entity-type="CONSUMERACCOUNT"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="INTEREST_RATES">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT1">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT7">

<token-value>

<value>191.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT5">

<token-value>

<value>101.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="FIN_INFO2">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_UNPAID_INT">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIMIT9">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIM10">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_OUTS_UNEARN">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="ACCNT_INFO">

<token-value>

<list-of-values>

<entity-descriptor entity-id="10" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_ACCOUNT_NO_OPEN_DATE">

<token-value>

<value>04/07/2015</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_DPD">

<token-value>

<value>10</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_NO_ACCT_NUM">

<token-value>

<value>00027066394534</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_ACCT_NAME">

<token-value>

<value>Adnan khalid</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR1">

<token-value>

<value>89/484</value>

</token-value>

</token-value-entry>

</template-data-map>';

DECLARE @ETL_STG_LETTER_RQST TABLE (dcmnt_rqst_id int, dcmnt_mppng_data_txt xml);

INSERT @ETL_STG_LETTER_RQST VALUES (1,@xml);

The solution that you posted could be simplified like this:

WITH XMLNAMESPACES ('http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0' AS NS)

SELECT

x.dcmnt_rqst_id,

Ent.value('(@token-name)[1]', 'varchar(20)'),

Ent.value('(NS:token-value)[1]','nvarchar(100)')

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry') AS T2(Ent)

Now, you have 4 token-value-entry nodes... three of them (RECIPIENT_ADD1, RECIPIENT_ADD2 & RECIPIENT_ADD3) have the child element token-value and it has a child element named value. CNSMR_ACCNTS, however, has a more complex structure which is screwing you up; its child element is list-of-values. We can exclude that node/element by modifying the nodes() function like this:

dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[not(NS:token-value/NS:list-of-values)]'

The whole query would look like this:

WITH XMLNAMESPACES ('http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0' AS NS)

SELECT

x.dcmnt_rqst_id,

Ent.value('(@token-name)[1]', 'varchar(20)'),

Ent.value('(NS:token-value)[1]','nvarchar(100)')

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[not(NS:token-value/NS:list-of-values)]') AS T2(Ent)

The query to get all of your values for the CNSMR_ACCNTS node would look like this:

SELECT

x.dcmnt_rqst_id,

Ent.value('(@token-name)[1]', 'varchar(20)'),

Ent2.value('(text())[1]','nvarchar(100)')

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[(NS:token-value/NS:list-of-values)]') AS T2(Ent)

CROSS APPLY Ent.nodes('NS:token-value//NS:value') AS T3(Ent2);

And the final solution including the sample data:

DECLARE @xml xml =

'<?xml version="1.0" encoding="UTF-8"?>

<template-data-map xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0">

<entity-descriptor entity-id="22" entity-type="CONSUMER"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="RECIPIENT_ADDR3">

<token-value>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR2">

<token-value>

<value>à¸–.à¸ªà¸¸à¸‚à¸¸à¸¡à¸§à¸´à¸—</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="CNSMR_ACCNTS">

<token-value>

<list-of-values>

<entity-descriptor entity-id="26" entity-type="CONSUMERACCOUNT"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="INTEREST_RATES">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT1">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT7">

<token-value>

<value>191.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT5">

<token-value>

<value>101.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="FIN_INFO2">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_UNPAID_INT">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIMIT9">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIM10">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_OUTS_UNEARN">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="ACCNT_INFO">

<token-value>

<list-of-values>

<entity-descriptor entity-id="10" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_ACCOUNT_NO_OPEN_DATE">

<token-value>

<value>04/07/2015</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_DPD">

<token-value>

<value>10</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_NO_ACCT_NUM">

<token-value>

<value>00027066394534</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_ACCT_NAME">

<token-value>

<value>Adnan khalid</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR1">

<token-value>

<value>89/484</value>

</token-value>

</token-value-entry>

</template-data-map>';

DECLARE @ETL_STG_LETTER_RQST TABLE (dcmnt_rqst_id int, dcmnt_mppng_data_txt xml);

INSERT @ETL_STG_LETTER_RQST VALUES (1,@xml);

WITH XMLNAMESPACES ('http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0' AS NS)

SELECT

x.dcmnt_rqst_id,

Ent.value('(@token-name)[1]', 'varchar(20)'),

Ent.value('(NS:token-value)[1]','nvarchar(100)')

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[not(NS:token-value/NS:list-of-values)]') AS T2(Ent)

UNION ALL

SELECT

x.dcmnt_rqst_id,

Ent.value('(@token-name)[1]', 'varchar(20)'),

Ent2.value('(text())[1]','nvarchar(100)')

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[(NS:token-value/NS:list-of-values)]') AS T2(Ent)

CROSS APPLY Ent.nodes('NS:token-value//NS:value') AS T3(Ent2);

Let us know if that helps. Cheers!

Edit/Update: our posts crossed paths, I missed your DDL post (thanks for that). My solution above, however, should give you what you need.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 4

Also,dr.dcmnt_rqst_id can be kept as any value.

Here is what i am expecting the result out of the same.

Current Result:

dcmnt_rqst_id token_name token_value

6 RECIPIENT_ADDR3 ?????????? ??????????

6 RECIPIENT_ADDR2 ?.????????

6 CNSMR_ACCNTS false6false60.00191.00101.00false61200.000.000.001200.00false604/07/20151000027066394534Adnan khalid

6 RECIPIENT_ADDR 189/484

Expected result

dcmnt_rqst_id token_name token_value

6 RECIPIENT_ADDR3 ?????????? ??????????

6 RECIPIENT_ADDR2 ?.????????

6 U_INTEREST_RATE_CREDIT_LIMIT 10.00

6 U_INTEREST_RATE_CREDIT_LIMIT7 191.00

6 U_INTEREST_RATE_CREDIT_LIMIT5 101.00

etc....

6 RECIPIENT_ADDR 189/484

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 5

Thanks

i am not so expert on the XML and i am using the query above. However i am getting a below error definitely, i am missing something here

"Msg 9506, Level 16, State 1, Line 1

The XMLDT method 'nodes' can only be invoked on columns of type xml."

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 6

HI Alan Thanks for that quick help.

i really appreciated. well, i am actually able to retrieve the data

However, the data is coming with the same token Names for all of the list of values. can i get the data value along-with its actual Token Names as i stated in the expected result above. Appreciate your help.

Alan Burstein SSC Guru Points: 61152 More actions · Answer 7

pawan.clong10 (4/8/2015)
HI Alan Thanks for that quick help.
i really appreciated. well, i am actually able to retrieve the data
However, the data is coming with the same token Names for all of the list of values. can i get the data value along-with its actual Token Names as i stated in the expected result above. Appreciate your help.

ahhh, I see... I think I can spin that up...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61152 More actions · Answer 8

Alan.B (4/8/2015)
pawan.clong10 (4/8/2015)
HI Alan Thanks for that quick help.
i really appreciated. well, i am actually able to retrieve the data
However, the data is coming with the same token Names for all of the list of values. can i get the data value along-with its actual Token Names as i stated in the expected result above. Appreciate your help.
ahhh, I see... I think I can spin that up...

How about this:

DECLARE @xml xml =

'<?xml version="1.0" encoding="UTF-8"?>

<template-data-map xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0">

<entity-descriptor entity-id="22" entity-type="CONSUMER"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="RECIPIENT_ADDR3">

<token-value>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR2">

<token-value>

<value>à¸–.à¸ªà¸¸à¸‚à¸¸à¸¡à¸§à¸´à¸—</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="CNSMR_ACCNTS">

<token-value>

<list-of-values>

<entity-descriptor entity-id="26" entity-type="CONSUMERACCOUNT"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="INTEREST_RATES">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT1">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT7">

<token-value>

<value>191.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT5">

<token-value>

<value>101.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="FIN_INFO2">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_UNPAID_INT">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIMIT9">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIM10">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_OUTS_UNEARN">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="ACCNT_INFO">

<token-value>

<list-of-values>

<entity-descriptor entity-id="10" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_ACCOUNT_NO_OPEN_DATE">

<token-value>

<value>04/07/2015</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_DPD">

<token-value>

<value>10</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_NO_ACCT_NUM">

<token-value>

<value>00027066394534</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_ACCT_NAME">

<token-value>

<value>Adnan khalid</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR1">

<token-value>

<value>89/484</value>

</token-value>

</token-value-entry>

</template-data-map>';

DECLARE @ETL_STG_LETTER_RQST TABLE (dcmnt_rqst_id int, dcmnt_mppng_data_txt xml);

INSERT @ETL_STG_LETTER_RQST VALUES (1,@xml);

WITH XMLNAMESPACES ('http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0' AS NS)

SELECT

x.dcmnt_rqst_id,

Ent.value('(@token-name)[1]', 'varchar(20)') AS token_name,

Ent.value('(NS:token-value)[1]','nvarchar(100)') AS token_value

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[not(NS:token-value/NS:list-of-values)]') AS T2(Ent)

UNION ALL

SELECT

x.dcmnt_rqst_id,

Ent2.value('(../../@token-name)[1]','nvarchar(100)'),

Ent2.value('(text())[1]','nvarchar(100)')

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[(NS:token-value/NS:list-of-values)]') AS T2(Ent)

OUTER APPLY Ent.nodes('NS:token-value//NS:value') AS T3(Ent2);

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pawan.clong10 SSC Enthusiast Points: 136 More actions · Answer 9

pawan.clong10

SSC Enthusiast

Points: 136

April 8, 2015 at 2:14 pm

#1789693

Thanks Alan, you are brilliant!!!

Alan Burstein SSC Guru Points: 61152 More actions · Answer 10

Thanks!

I actually had a few moments to kill and optimized that query further; this will be much, much faster, uses less code and creates a simplified query plan...

DECLARE @xml xml =

'<?xml version="1.0" encoding="UTF-8"?>

<template-data-map xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0">

<entity-descriptor entity-id="22" entity-type="CONSUMER"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="RECIPIENT_ADDR3">

<token-value>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR2">

<token-value>

<value>à¸–.à¸ªà¸¸à¸‚à¸¸à¸¡à¸§à¸´à¸—</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="CNSMR_ACCNTS">

<token-value>

<list-of-values>

<entity-descriptor entity-id="26" entity-type="CONSUMERACCOUNT"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="INTEREST_RATES">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT1">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT7">

<token-value>

<value>191.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT5">

<token-value>

<value>101.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="FIN_INFO2">

<token-value>

<list-of-values>

<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_UNPAID_INT">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIMIT9">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIM10">

<token-value>

<value>0.00</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_OUTS_UNEARN">

<token-value>

<value>1200.00</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="ACCNT_INFO">

<token-value>

<list-of-values>

<entity-descriptor entity-id="10" entity-type="USER_DEFINED"/>

<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">

<staging-udp>false</staging-udp>

</recipient-entity-descriptor>

<mapping-set-id>6</mapping-set-id>

<token-value-entry token-name="U_ACCOUNT_NO_OPEN_DATE">

<token-value>

<value>04/07/2015</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_DPD">

<token-value>

<value>10</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_NO_ACCT_NUM">

<token-value>

<value>00027066394534</value>

</token-value>

</token-value-entry>

<token-value-entry token-name="U_ACCOUNT_INFO_ACCT_NAME">

<token-value>

<value>Adnan khalid</value>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

</list-of-values>

</token-value>

</token-value-entry>

<token-value-entry token-name="RECIPIENT_ADDR1">

<token-value>

<value>89/484</value>

</token-value>

</token-value-entry>

</template-data-map>';

DECLARE @ETL_STG_LETTER_RQST TABLE (dcmnt_rqst_id int, dcmnt_mppng_data_txt xml);

INSERT @ETL_STG_LETTER_RQST VALUES (1,@xml);

/*** VESION 3 (100X improvement over v2) ***/

WITH XMLNAMESPACES ('http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0' AS NS)

SELECT

x.dcmnt_rqst_id,

CASE Ent.value('(NS:token-value[1]/NS:list-of-values[1]//text()[1])[1]','bit')

WHEN NULL THEN Ent.value('(NS:token-value)[1]','nvarchar(100)')

ELSE Ent2.value('(../../@token-name)[1]','nvarchar(100)')

END AS token_name,

CASE Ent.value('(NS:token-value[1]/NS:list-of-values[1]//text()[1])[1]','bit')

WHEN NULL THEN Ent.value('(NS:token-value)[1]','nvarchar(100)')

ELSE Ent2.value('(text())[1]','nvarchar(100)')

END AS token_value

FROM @ETL_STG_LETTER_RQST x

CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map[1]/NS:token-value-entry') AS T2(Ent)

OUTER APPLY Ent.nodes('NS:token-value[1]//NS:value') AS T3(Ent2);

edit: used the wrong code tag

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 11

Alan.B (4/8/2015)
Alan.B (4/8/2015)
pawan.clong10 (4/8/2015)
HI Alan Thanks for that quick help.
i really appreciated. well, i am actually able to retrieve the data
However, the data is coming with the same token Names for all of the list of values. can i get the data value along-with its actual Token Names as i stated in the expected result above. Appreciate your help.
ahhh, I see... I think I can spin that up...
How about this:
DECLARE @xml xml =
'<?xml version="1.0" encoding="UTF-8"?>
<template-data-map xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0">
<entity-descriptor entity-id="22" entity-type="CONSUMER"/>
<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">
<staging-udp>false</staging-udp>
</recipient-entity-descriptor>
<mapping-set-id>6</mapping-set-id>
<token-value-entry token-name="RECIPIENT_ADDR3">
<token-value>
<value>à¹à¸‚à¸§à¸‡à¸šà¸²à¸‡à¸ˆà¸²à¸ à¹€à¸‚à¸•à¸žà¸£à¸°à¹‚à¸‚à¸™à¸‡</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="RECIPIENT_ADDR2">
<token-value>
<value>à¸–.à¸ªà¸¸à¸‚à¸¸à¸¡à¸§à¸´à¸—</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="CNSMR_ACCNTS">
<token-value>
<list-of-values>
<entity-descriptor entity-id="26" entity-type="CONSUMERACCOUNT"/>
<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">
<staging-udp>false</staging-udp>
</recipient-entity-descriptor>
<mapping-set-id>6</mapping-set-id>
<token-value-entry token-name="INTEREST_RATES">
<token-value>
<list-of-values>
<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>
<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">
<staging-udp>false</staging-udp>
</recipient-entity-descriptor>
<mapping-set-id>6</mapping-set-id>
<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT1">
<token-value>
<value>0.00</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT7">
<token-value>
<value>191.00</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_INTEREST_RATE_CREDIT_LIMIT5">
<token-value>
<value>101.00</value>
</token-value>
</token-value-entry>
</list-of-values>
</token-value>
</token-value-entry>
<token-value-entry token-name="FIN_INFO2">
<token-value>
<list-of-values>
<entity-descriptor entity-id="1" entity-type="USER_DEFINED"/>
<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">
<staging-udp>false</staging-udp>
</recipient-entity-descriptor>
<mapping-set-id>6</mapping-set-id>
<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_UNPAID_INT">
<token-value>
<value>1200.00</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIMIT9">
<token-value>
<value>0.00</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_FINANCIAL_INFO2_CREDIT_LIM10">
<token-value>
<value>0.00</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_FINANCIAL_INFO2_TOT_OUTS_UNEARN">
<token-value>
<value>1200.00</value>
</token-value>
</token-value-entry>
</list-of-values>
</token-value>
</token-value-entry>
<token-value-entry token-name="ACCNT_INFO">
<token-value>
<list-of-values>
<entity-descriptor entity-id="10" entity-type="USER_DEFINED"/>
<recipient-entity-descriptor entity-id="22" entity-type="CONSUMER">
<staging-udp>false</staging-udp>
</recipient-entity-descriptor>
<mapping-set-id>6</mapping-set-id>
<token-value-entry token-name="U_ACCOUNT_NO_OPEN_DATE">
<token-value>
<value>04/07/2015</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_ACCOUNT_INFO_DPD">
<token-value>
<value>10</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_ACCOUNT_NO_ACCT_NUM">
<token-value>
<value>00027066394534</value>
</token-value>
</token-value-entry>
<token-value-entry token-name="U_ACCOUNT_INFO_ACCT_NAME">
<token-value>
<value>Adnan khalid</value>
</token-value>
</token-value-entry>
</list-of-values>
</token-value>
</token-value-entry>
</list-of-values>
</token-value>
</token-value-entry>
<token-value-entry token-name="RECIPIENT_ADDR1">
<token-value>
<value>89/484</value>
</token-value>
</token-value-entry>
</template-data-map>';
DECLARE @ETL_STG_LETTER_RQST TABLE (dcmnt_rqst_id int, dcmnt_mppng_data_txt xml);
INSERT @ETL_STG_LETTER_RQST VALUES (1,@xml);
WITH XMLNAMESPACES ('http://www.crsoftwareinc.com/xml/ns/titanium/document/v1_0' AS NS)
SELECT
x.dcmnt_rqst_id,
Ent.value('(@token-name)[1]', 'varchar(20)') AS token_name,
Ent.value('(NS:token-value)[1]','nvarchar(100)') AS token_value
FROM @ETL_STG_LETTER_RQST x
CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[not(NS:token-value/NS:list-of-values)]') AS T2(Ent)
UNION ALL
SELECT
x.dcmnt_rqst_id,
Ent2.value('(../../@token-name)[1]','nvarchar(100)'),
Ent2.value('(text())[1]','nvarchar(100)')
FROM @ETL_STG_LETTER_RQST x
CROSS APPLY dcmnt_mppng_data_txt.nodes('/NS:template-data-map/NS:token-value-entry[(NS:token-value/NS:list-of-values)]') AS T2(Ent)
OUTER APPLY Ent.nodes('NS:token-value//NS:value') AS T3(Ent2);

Heh... "How doust I hate XML? Let me count the ways..."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)