XML Workshop XVII - Writing a LOOP to process XML elements in TSQL
-
Comments posted to this topic are about the item XML Workshop XVII - Writing a LOOP to process XML elements in TSQL
.
-
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)
-
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)
-
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
-
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:"
-
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);
-
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 -
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
-
After the .nodes() method you must create a table and column alias. e is the table alias, c is the column alias.
-
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)
-
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)
-
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).
-
cmille19 (3/26/2008)
Another way using xquery if you have multiple Name elements as noted in this blog entryhttp://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
-
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);
-
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 =)
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply