Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

XML Workshop XVII - Writing a LOOP to process XML elements in TSQL Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2008 11:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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)

Post #474958
Posted Wednesday, March 26, 2008 12:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:25 PM
Points: 59, 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).
Post #474981
Posted Wednesday, March 26, 2008 12:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:52 AM
Points: 74, Visits: 450
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... ;)


Christopher Ford
Post #475003
Posted Wednesday, March 26, 2008 12:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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);

Post #475013
Posted Wednesday, March 26, 2008 1:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:25 PM
Points: 59, 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 =)
Post #475036
Posted Wednesday, March 26, 2008 1:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:52 AM
Points: 74, Visits: 450
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
Post #475058
Posted Wednesday, March 26, 2008 2:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #475083
Posted Wednesday, March 26, 2008 2:32 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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 :) 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.
Post #475086
Posted Wednesday, March 26, 2008 3:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:02 PM
Points: 259, Visits: 716
That is much simplier than the looping Xquery I used -- Thanks


Post #475107
Posted Wednesday, March 26, 2008 3:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:49 AM
Points: 147, Visits: 196
Thank you, thank you, thank you. Written like a book. Great job.

-Mike
Post #475113
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse