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

Question

Post reply

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

jacob sebastian

SSChampion

Points: 11822
More actions
March 25, 2008 at 9:50 pm

#128391

Comments posted to this topic are about the item XML Workshop XVII - Writing a LOOP to process XML elements in TSQL
.

Viewing 15 posts - 1 through 15 (of 49 total)

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

sanjeev.rategain SSC Enthusiast Points: 105 More actions · Answer 1

Cool One.!!

Another way to fetch the XML Element without loop will be by using the XQuery function Text.

For same xml this will work.

SELECT T.c.value('(Name/text())[1]', 'varchar(256)') as Name,T.c.value('(Department/text())[1]', 'varchar(256)') as Department

FROM @x.nodes('/Employees/Employee') T(c)

cmille19 SSCertifiable Points: 5970 More actions · Answer 2

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)

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 3

Does anyone know whether the XML or T-SQL count() functions are equivalent in execution, or is one more efficient than the other.

Is there any significant difference between...

SELECT

@max-2 = @x.query('

{ count(/Employees/Employee) }

').value('e[1]','int')

and

select @max-2 = count(*) from

@x.nodes('/Employees/Employee')

e(x)

?

The query plans indicate that the cost of the first is twice the cost of the second. Is this true in practice?

Derek

Robert Seliga SSC Journeyman Points: 92 More actions · Answer 4

just do this to retrieve the element count for the loop - we've had to do this a lot..

select @max-2 = @x.value('fn:count(/Employees/Employee)','int')

or you could even include the xpath in the while loop definition:

while @i <= (@x.value('fn:count(/Employees/Employee)','int'))

begin

print @i

set @i = @i + 1

end

there are a lot of fn: xpath functions that work really well for different situations - BOL has a listing of them, just search for "fn:"

Mike C SSC-Insane Points: 23224 More actions · Answer 5

You can retrieve all names without a CTE and CROSS APPLY as well:

DECLARE @x XML;

SET @x = '<Employees>

<Employee ID="101">

<Name>Jacob</Name>

<Department>IT</Department>

</Employee>

<Employee ID="354">

<Name>Steve</Name>

<Department>IT</Department>

</Employee>

<Employee ID="456">

<Name>Bob</Name>

<Department>IT</Department>

</Employee>

<Employee ID="478">

<Name>Joe</Name>

<Department>IT</Department>

</Employee>

<Employee ID="981">

<Name>Louis</Name>

<Department>IT</Department>

</Employee>

</Employees>';

-- select "Name"

WITH Num(i)

AS

(

SELECT 1

UNION ALL

SELECT i + 1

FROM Num

WHERE i < (SELECT @x.value('count(/Employees/Employee)','int') )

)

SELECT x.value('Name[1]', 'VARCHAR(20)')

FROM Num

CROSS APPLY @x.nodes('/Employees/Employee[position()=sql:column("i")]') e(x);

That's if you really want to use the position() function. Without using position() it gets a little simpler:

SELECT x.value('Name[1]', 'VARCHAR(20)')

FROM @x.nodes('/Employees/Employee') e(x);

Jerry Hung SSChampion Points: 12968 More actions · Answer 6

This has been helpful, after I spent 0.5 day yesterday trying to figure out XQuery in T-SQL as DBA

in the end, I cast XML to VARCHAR and PATINDEX it....

Can anybody show/confirm how I can do this as SET operation on a table T with X as a XML column, instead of working on 1 XML @x at a time

e.g. can I do this to get all Names, say in a department table T that has an Employee XML column X?

SELECT x.value('Name[1]', 'VARCHAR(20)')

FROM T.X.nodes('/Employees/Employee') e(x);

Thanks in advance

I really need a quick lesson in XQuery

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

MudLuck SSCrazy Points: 2301 More actions · Answer 7

Sorry to be Lazy or maybe just an american?

Anyway I know there is a meaning behind this in the code.

e(x)

I just can't seem to figure out what. One of the Replies used T(c) instead. I'm sure it's some type of XML formatting paramater I just have no idea and am curious.

Thanks,

M

Mike C SSC-Insane Points: 23224 More actions · Answer 8

After the .nodes() method you must create a table and column alias. e is the table alias, c is the column alias.

DB_Andrew Ten Centuries Points: 1089 More actions · Answer 9

This is more elegant.

SELECT

Table1.Column1.value('.','VARCHAR(20)') AS Employee_Name

FROM

@XML_Employee_Name.nodes('/Employees/Employee/Name[1]') AS Table1(Column1)

Mike C SSC-Insane Points: 23224 More actions · Answer 10

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 Ten Centuries Points: 1089 More actions · Answer 11

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 SSCommitted Points: 1622 More actions · Answer 12

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

Mike C SSC-Insane Points: 23224 More actions · Answer 13

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 Ten Centuries Points: 1089 More actions · Answer 14

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

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

Cookies on SQLServerCentral