Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jacob Sebastian XML Workshop XVII - Writing a LOOP to process XML elements in TSQL RE: XML Workshop XVII - Writing a LOOP to process XML elements in TSQL

  • March 26, 2008 at 12:28 pm

    #794270

    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= '

    &ltCREDITREPORT CREDITREPORTID="CRRept0001" CreditReportType="Merge" MergeType="ListAndStack" EquifaxIncludedIndicator="N"

    ExperianIncludedIndicator="Y" TransUnionIncludedIndicator="N"&gt

    &ltCreditReportIdentifier&gtxxxxx_xxx938&lt/CreditReportIdentifier&gt

    &ltLastUpdatedDate&gt2006-04-10&lt/LastUpdatedDate&gt

    &ltMERGEDLIABILITY 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=""&gt

    &ltAccountIdentifier&gt******5563&lt/AccountIdentifier&gt

    &ltAccountOpenedDate&gt2002-01&lt/AccountOpenedDate&gt

    &ltAccountStatusDate&gt2002-12&lt/AccountStatusDate&gt

    &ltCreditorName&gtOLYMPUS&lt/CreditorName&gt

    &ltHighBalanceAmount&gt107200&lt/HighBalanceAmount&gt

    &ltHighCreditAmount&gt107200&lt/HighCreditAmount&gt

    &ltLate30DaysCount&gt0&lt/Late30DaysCount&gt

    &ltLate60DaysCount&gt0&lt/Late60DaysCount&gt

    &ltLate90DaysCount&gt0&lt/Late90DaysCount&gt

    &ltMonthsReviewedCount&gt13&lt/MonthsReviewedCount&gt

    &ltPaymentPatternData&gtNCCCCCCCCCCCC&lt/PaymentPatternData&gt

    &ltPaymentPatternStartDate&gt2002-12&lt/PaymentPatternStartDate&gt

    &ltReportedDate&gt2002-12&lt/ReportedDate&gt

    &ltTermMonths&gt360&lt/TermMonths&gt

    &ltCREDITCOMMENT CommentSource="RepositoryBureau"&gt

    &ltComment&gtAccount transferred to another lender&lt/Comment&gt

    &lt/CREDITCOMMENT&gt

    &ltCREDITCOMMENT CommentSource="RepositoryBureau"&gt

    &ltComment&gtBaseStatus code `05` - TRANSFER&lt/Comment&gt

    &lt/CREDITCOMMENT&gt

    &lt/MERGEDLIABILITY&gt

    &lt/CREDITREPORT&gt '

    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