SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop XVII - Writing a LOOP to process XML elements in TSQL


XML Workshop XVII - Writing a LOOP to process XML elements in TSQL

Author
Message
Mike C
Mike C
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2587 Visits: 1168
Or for this particular XML data you could simplify even further:


SELECT
Table1.Column1.value('.','VARCHAR(20)') AS Employee_Name
FROM
@XML_Employee_Name.nodes('//Name[1]') AS Table1(Column1)


DB_Andrew
DB_Andrew
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 271
Correct. It could be simplified even more.

It comes down to coding standards, personal preference and style.

It functions and performs the same as the following.

SELECT t.c.value('.','VARCHAR(20)') AS [Name] FROM @X.nodes('//Name[1]') AS t(c)

But if I wrote code like that then I'd have to comment for it my peers and superiors. So I try to right code so it is easier to understand and some what self documenting (If there is such a thing).
Christopher Ford-327546
Christopher Ford-327546
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 454
cmille19 (3/26/2008)
Another way using xquery if you have multiple Name elements as noted in this blog entry
http://blogs.msdn.com/mrorke/archive/2005/07/21/441554.aspx

SELECT T.ref.value('.', 'varchar(256)') AS Employee
FROM
(
select [Xml]=@x.query('
for $i in data(/Employees/Employee/Name) return
element temp { $i }
')
) A
cross apply A.Xml.nodes('/temp') T(ref)


I think this is something along the lines of what I'm trying to do...

But what if I want lots of different nodes or elements?

Example XML:


SET @x= '
<CREDITREPORT CREDITREPORTID="CRRept0001" CreditReportType="Merge" MergeType="ListAndStack" EquifaxIncludedIndicator="N"
ExperianIncludedIndicator="Y" TransUnionIncludedIndicator="N">
<CreditReportIdentifier>xxxxx_xxx938</CreditReportIdentifier>
<LastUpdatedDate>2006-04-10</LastUpdatedDate>
<MERGEDLIABILITY ScoreTypeCode="Non" IndustryCode="FB" ICText="Mortgage Brokers" CollectionCode="" CollectionText=""
MERGEDLIABILITYID="xxxxx_1_12" AccountOwnershipType="Individual" AccountStatusType="Transferred" AccountType="Mortgage"
BusinessType="Finance" CreditLoanType="ConventionalRealEstateMortgage" CurrentDelinquencyRatingType="AsAgreed"
ConsumerDisputeIndicator="N" DerogatoryDataIndicator="Y" BORROWERIDREFS="">
<AccountIdentifier>******5563</AccountIdentifier>
<AccountOpenedDate>2002-01</AccountOpenedDate>
<AccountStatusDate>2002-12</AccountStatusDate>
<CreditorName>OLYMPUS</CreditorName>
<HighBalanceAmount>107200</HighBalanceAmount>
<HighCreditAmount>107200</HighCreditAmount>
<Late30DaysCount>0</Late30DaysCount>
<Late60DaysCount>0</Late60DaysCount>
<Late90DaysCount>0</Late90DaysCount>
<MonthsReviewedCount>13</MonthsReviewedCount>
<PaymentPatternData>NCCCCCCCCCCCC</PaymentPatternData>
<PaymentPatternStartDate>2002-12</PaymentPatternStartDate>
<ReportedDate>2002-12</ReportedDate>
<TermMonths>360</TermMonths>
<CREDITCOMMENT CommentSource="RepositoryBureau">
<Comment>Account transferred to another lender</Comment>
</CREDITCOMMENT>
<CREDITCOMMENT CommentSource="RepositoryBureau">
<Comment>BaseStatus code `05` - TRANSFER</Comment>
</CREDITCOMMENT>
</MERGEDLIABILITY>
</CREDITREPORT> '



What if I want to iterate through 1000 rows in SQL, with each row containing XML like this, and each XML document will have 4+ MergedLiability nodes and I want data in those nodes?

That's where I get confused on the looping stuff.

And uhh..yeah...this is a ... uhm... fictitious example...yeah...has nothing to do with my job...nothing at all...I dreamed up the complicated XML example...all by myself...sure... Wink

Christopher Ford

Mike C
Mike C
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2587 Visits: 1168
For readability purposes I'd probably go with something like this:


SELECT x.value('Name[1]', 'VARCHAR(20)')
FROM @x.nodes('/Employees/Employee') e(x);


DB_Andrew
DB_Andrew
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 271
Mike C (3/26/2008)
For readability purposes I'd probably go with something like this:


SELECT x.value('Name[1]', 'VARCHAR(20)')
FROM @x.nodes('/Employees/Employee') e(x);




I like this method. It is actually faster and uses less resources as it applies the XPathFilter.

Thanks for the tip =)
Christopher Ford-327546
Christopher Ford-327546
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 454
The light finally went off...

for each "section" I need to cross apply it to do what I want.

Example below:


SELECT
x.value('UnparsedName[1]', 'VARCHAR(20)') as Name
,l.value('@BusinessType', 'varchar(20)') as BusinessType
,CAST(CONVERT(varchar(20),l.query('HighBalanceAmount/text()')) as money) as HighBalanceAmount
FROM ScreeningXML myXML
CROSS APPLY xmlNode.nodes('/MORTGAGEDATA/BORROWER') E(x)
CROSS APPLY xmlNode.nodes('/MORTGAGEDATA/CREDITREPORT/MERGEDLIABILITY') M(l)



Christopher Ford

Mike C
Mike C
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2587 Visits: 1168
Christopher Ford (3/26/2008)
The light finally went off...

for each "section" I need to cross apply it to do what I want.

Example below:


SELECT
x.value('UnparsedName[1]', 'VARCHAR(20)') as Name
,l.value('@BusinessType', 'varchar(20)') as BusinessType
,CAST(CONVERT(varchar(20),l.query('HighBalanceAmount/text()')) as money) as HighBalanceAmount
FROM ScreeningXML myXML
CROSS APPLY xmlNode.nodes('/MORTGAGEDATA/BORROWER') E(x)
CROSS APPLY xmlNode.nodes('/MORTGAGEDATA/CREDITREPORT/MERGEDLIABILITY') M(l)





Sorry I just saw your previous question. I think I misunderstood your question though. Is borrower related to CREDITREPORT in any way? If so, it's not readily apparent (to me anyway) from your XQuery. I thought you were asking how to query multiple MERGEDLIABILITY nodes under a single CREDITRPORT node, like this:


DECLARE @x xml;

SET @x= '
<CREDITREPORT CREDITREPORTID="CRRept0001" CreditReportType="Merge" MergeType="ListAndStack" EquifaxIncludedIndicator="N"
ExperianIncludedIndicator="Y" TransUnionIncludedIndicator="N">
<CreditReportIdentifier>xxxxx_xxx938</CreditReportIdentifier>
<LastUpdatedDate>2006-04-10</LastUpdatedDate>
<MERGEDLIABILITY ScoreTypeCode="Non" IndustryCode="FB" ICText="Mortgage Brokers" CollectionCode="" CollectionText=""
MERGEDLIABILITYID="xxxxx_1_12" AccountOwnershipType="Individual" AccountStatusType="Transferred" AccountType="Mortgage"
BusinessType="Finance" CreditLoanType="ConventionalRealEstateMortgage" CurrentDelinquencyRatingType="AsAgreed"
ConsumerDisputeIndicator="N" DerogatoryDataIndicator="Y" BORROWERIDREFS="">
<AccountIdentifier>******5563</AccountIdentifier>
<AccountOpenedDate>2002-01</AccountOpenedDate>
<AccountStatusDate>2002-12</AccountStatusDate>
<CreditorName>OLYMPUS</CreditorName>
<HighBalanceAmount>107200</HighBalanceAmount>
<HighCreditAmount>107200</HighCreditAmount>
<Late30DaysCount>0</Late30DaysCount>
<Late60DaysCount>0</Late60DaysCount>
<Late90DaysCount>0</Late90DaysCount>
<MonthsReviewedCount>13</MonthsReviewedCount>
<PaymentPatternData>NCCCCCCCCCCCC</PaymentPatternData>
<PaymentPatternStartDate>2002-12</PaymentPatternStartDate>
<ReportedDate>2002-12</ReportedDate>
<TermMonths>360</TermMonths>
<CREDITCOMMENT CommentSource="RepositoryBureau">
<Comment>Account transferred to another lender</Comment>
</CREDITCOMMENT>
<CREDITCOMMENT CommentSource="RepositoryBureau">
<Comment>BaseStatus code `05` - TRANSFER</Comment>
</CREDITCOMMENT>
</MERGEDLIABILITY>
<MERGEDLIABILITY ScoreTypeCode="Non" IndustryCode="FB" ICText="Mortgage Brokers" CollectionCode="" CollectionText=""
MERGEDLIABILITYID="yyyyy_1_13" AccountOwnershipType="Individual" AccountStatusType="Transferred" AccountType="Mortgage"
BusinessType="Finance" CreditLoanType="ConventionalRealEstateMortgage" CurrentDelinquencyRatingType="AsAgreed"
ConsumerDisputeIndicator="N" DerogatoryDataIndicator="Y" BORROWERIDREFS="">
<AccountIdentifier>******5563</AccountIdentifier>
<AccountOpenedDate>2003-01</AccountOpenedDate>
<AccountStatusDate>2004-09</AccountStatusDate>
<CreditorName>OLYMPUS</CreditorName>
<HighBalanceAmount>999999</HighBalanceAmount>
<HighCreditAmount>999999</HighCreditAmount>
<Late30DaysCount>0</Late30DaysCount>
<Late60DaysCount>0</Late60DaysCount>
<Late90DaysCount>0</Late90DaysCount>
<MonthsReviewedCount>13</MonthsReviewedCount>
<PaymentPatternData>NCCCCCCCCCCCC</PaymentPatternData>
<PaymentPatternStartDate>2002-12</PaymentPatternStartDate>
<ReportedDate>2002-12</ReportedDate>
<TermMonths>360</TermMonths>
<CREDITCOMMENT CommentSource="RepositoryBureau">
<Comment>Account transferred to another lender</Comment>
</CREDITCOMMENT>
<CREDITCOMMENT CommentSource="RepositoryBureau">
<Comment>BaseStatus code `05` - TRANSFER</Comment>
</CREDITCOMMENT>
</MERGEDLIABILITY>
</CREDITREPORT> ';

SELECT creditreport.value('@CREDITREPORTID[1]', 'varchar(100)') AS creditreportid,
creditreport.value('LastUpdatedDate[1]', 'datetime') AS lastupdated,
mergedliability.value('AccountOpenedDate[1]', 'varchar(100)') AS opendate,
mergedliability.value('AccountStatusDate[1]', 'varchar(100)') AS statusdate,
mergedliability.value('HighBalanceAmount[1]', 'varchar(100)') AS highbalance
FROM @x.nodes('/CREDITREPORT') AS cr(creditreport)
CROSS APPLY creditreport.nodes('MERGEDLIABILITY') AS ml(mergedliability);



Note that you can cross apply the results of the .nodes() method and apply another .nodes() method to them like in this example. Also on this one I spelled out the column aliases to make them a little more readable.
Mike C
Mike C
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2587 Visits: 1168
Andrew Pfeiffer (3/26/2008)
Mike C (3/26/2008)
For readability purposes I'd probably go with something like this:


SELECT x.value('Name[1]', 'VARCHAR(20)')
FROM @x.nodes('/Employees/Employee') e(x);




I like this method. It is actually faster and uses less resources as it applies the XPathFilter.

Thanks for the tip =)


No problem Smile I was at a presentation that Michael Rys gave a while back and here are some more of his tips for making your XQuery as efficient as possible:

- Don't use the ".." axis if you can avoid it.
- Don't use "//" with the wildcard "*" in the middle of a path expression.
- Use [n] predicate filter to give the optimizer cardinality hints whenever possible (e.g., "[1]"). This forces the optimizer to make better decisions about which operators to use.

He actually covered about 10 more things, but I can't remember them all off the top of my head. Some were for very specific situations, and probably fairly uncommon.
cmille19
cmille19
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 724
That is much simplier than the looping Xquery I used -- Thanks



Mike DiRenzo
Mike DiRenzo
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 210
Thank you, thank you, thank you. Written like a book. Great job.

-Mike
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search