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

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

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

  • 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

  • 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.

  • 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:


    SELECT @x =


    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)



    SELECT 1


    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" />


    Any help will be appreciated.

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

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