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

Viewing 5 posts - 46 through 49 (of 49 total)

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

bhaskar.sanga Old Hand Points: 341 More actions · Answer 1

One more thing Jocob, my main purpose of this is.. I want add an element(ID) under each node Customer Node

bhaskar.sanga Old Hand Points: 341 More actions · Answer 2

bhaskar.sanga

Old Hand

Points: 341

June 9, 2010 at 9:51 am

#1178062

Never Mind Jocob...Thanks..I got it

vgermscheid SSC Enthusiast Points: 133 More actions · Answer 3

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

chintanmistry Grasshopper Points: 15 More actions · Answer 4

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.

pricejt Say Hey Kid Points: 671 More actions · Answer 5

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.