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 Tuesday, June 08, 2010 3:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 01, 2013 10:18 AM
Points: 9, Visits: 19
position()=sql:variable("@i") is not working for me, I am working on SQL 2005, basically I was trying to loop each node and insert an element..so if I use [1] or [2] its inserting in either first or second node, that's why I wanted to use while loop...in that I using the index as [position()=sql:variable("@i")] as well as [sql:variable("@i")] both are not working...please help..
Post #934270
Posted Tuesday, June 08, 2010 9:03 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Please post a small repro script that shows your problem.

.
Post #934360
Posted Wednesday, June 09, 2010 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 01, 2013 10:18 AM
Points: 9, Visits: 19
DECLARE @index int
SET @index = 0
DECLARE @charCount as varchar(1)
DECLARE @count int
SET @charcount = CAST(@CustomerData.query('count(/customer/customer)') as varchar)
SET @Count = CAST(@charCount as int)

WHILE @index < @count
BEGIN
DECLARE @ID2 uniqueidentifier
SET @ID2 = NewID()

SET @CustomerData.modify('insert element ID {"x"} before
(/customer/customer/firstName)[sql:variable("@index")]')
SET @CustomerData.modify('replace value of (/customer/customer/ID/text())[sql:variable("@index")] with sql:variable("@ID2")')

SET @index = @index + 1
END

Instead of sql:variable("@index") if I use 1 or 2 its working..my question how to get the parameterized index
Post #934599
Posted Wednesday, June 09, 2010 7:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
The syntax is not correct. The correct expression is [position()=sql:variable("@var")]. Here is an example:

DECLARE @x XML, @cnt INT
SELECT @x = '
<a>
<n>node 1</n>
<n>node 2</n>
<n>node 3</n>
</a>'

SELECT @cnt = 1
WHILE @cnt <= 3 BEGIN
SELECT @x.value('(/a/n[position()=sql:variable("@cnt")])[1]','VARCHAR(20)')
SELECT @cnt = @cnt + 1
END

/*
Prints

node 1
node 2
node 3
*/



.
Post #934607
Posted Wednesday, June 09, 2010 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 01, 2013 10:18 AM
Points: 9, Visits: 19
Jocb, This is not working with SET statement, Its always inserting an element under first node.

could you please convert my example and send it back...Thanks for your help
Post #934678
Posted Wednesday, June 09, 2010 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 01, 2013 10:18 AM
Points: 9, Visits: 19
One more thing Jocob, my main purpose of this is.. I want add an element(ID) under each node Customer Node
Post #934687
Posted Wednesday, June 09, 2010 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 01, 2013 10:18 AM
Points: 9, Visits: 19
Never Mind Jocob...Thanks..I got it
Post #934716
Posted Wednesday, November 03, 2010 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 5:10 AM
Points: 13, Visits: 235
Querying XML data in a set operation is relatively straight forward. Here's an excerpt from a query I have to retrieve content from a table with XML content, in this case subscriber information from a healthcare claim. First two columns in the query result are physical columns in the table, the rest come from the XML content.

Note that there's also a call to a custom assembly (NLXDecrypt) to decrypt values stored in the XML payload - portions of the content is stored encrypted to hide from prying eyes .

;WITH XMLNAMESPACES ('http://Novologix.Shared.Payload.Library.Domain.Schemas' AS cm)
SELECT TOP 100 ClaimTransactionId, AddDate,
claim.content.value('SubmitterClaimIdentifier[1]','varchar(max)') SubmitterClaim,
claim.content.value('cm:NLXEntityDiagnosis[1]/DiagnosisCode[1]','varchar(max)') PrimaryDiagnosis,
claim.content.value('count(cm:NLXEntityServiceLine)','int') Lines,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/MemberId[1]','varchar(max)')) MemberId,
claim.content.value('cm:NLXEntityMember[1]/ClaimRole[1]','varchar(max)') ClaimRole,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/DateOfBirth[1]','varchar(max)')) DOB,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/cm:NLXEntityPerson[1]/FirstName[1]','varchar(max)')) FirstName,
admindb.dbo.NLXDecrypt(claim.content.value('cm:NLXEntityMember[1]/cm:NLXEntityPerson[1]/LastName[1]','varchar(max)')) LastName,
claim.content.value('cm:NLXEntityMember[1]/cm:NLXEntityPerson[1]/Gender[1]','varchar(max)') Gender
FROM dbo.ClaimTransaction
CROSS APPLY NLXClaim.nodes('/cm:NLXTransactionClaim/cm:NLXEntityClaim') claim(content)
ORDER BY ClaimTransactionId DESC

Post #1015235
Posted Thursday, October 27, 2011 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 2:18 PM
Points: 1, Visits: 1
Hello,

How can I fetch the whole node without fetching individual fields...just like if you select it and gives you XML for single XML.
Post #1197121
Posted Tuesday, November 22, 2011 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 20, 2012 7:03 AM
Points: 9, Visits: 88
Ok i have this working however I still don't understand how to get all the values when my select returns more than 1 row. I assume I need a loop just not sure if there is a better way.

Here is what I have:
DECLARE @x XML;

SELECT @x =
r.XMLData
FROM RulesetGroups rg
INNER JOIN Rulesets rs on
rg.RulesetGroupID = rs.RulesetGroupID
INNER JOIN Rules r on
rs.RulesetID = r.RulesetID
WHERE r.RuleTypeID = 3 and r.XMLData.value('(/Screen/ScreenOption/@Name)[1]', 'varchar(255)') is not null;

-- select "Name"
WITH Num(i)
AS
(
SELECT 1
UNION ALL
SELECT i + 1
FROM Num
WHERE i < (SELECT @x.value('count(/Screen/ScreenOption/@Name)','varchar(255)') )
)
SELECT x.value('@Name[1]', 'varchar(20)')
FROM Num
CROSS APPLY @x.nodes('/Screen/ScreenOption[position()=sql:column("i")]') e(x);

My XML for one row. I will have hundreds of these though in different rows:
<Screen Title="">
<ScreenOption Sequence="1" Name="BTWidth" />
<ScreenOption Sequence="2" Name="BTHeight" />
</Screen>

Any help will be appreciated.
Post #1210429
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse