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

• bhaskar.sanga

  Old Hand

  Points: 341

  More actions

  June 9, 2010 at 9:21 am

  #1178040

  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

  June 9, 2010 at 9:51 am

  #1178062

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

• vgermscheid

  SSC Enthusiast

  Points: 133

  More actions

  November 3, 2010 at 8:07 am

  #1246008

  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

  October 27, 2011 at 11:34 am

  #1400318

  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

  November 22, 2011 at 11:52 am

  #1411733

  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.