need help parsing XML

• koolme_85

  Ten Centuries

  Points: 1153

  More actions

  July 21, 2014 at 1:55 pm

  #99059

  i have a Storedprocedure that returns an xml

  EXEC [dbo].[bofa_sp_esb_sel_xxx]

  @SearchType = N'A',

  @SearchInputList = N'57575757',

  @FAsInputList = null

  below is the XML

  <AccountDetail>

  <Account>

  <AccountNumber>12345678</AccountNumber>

  <ShortName>john</ShortName>

  <CategoryCode>ret</CategoryCode>

  <CategoryDescription>Retirement Account</CategoryDescription>

  <CategorySequenceNumber>1</CategorySequenceNumber>

  <SubCategoryCode>ret</SubCategoryCode>

  <SubCategoryDescription>Retirement Account</SubCategoryDescription>

  <ClassCode>ira</ClassCode>

  <ClassDescription>IRA</ClassDescription>

  <RegistrationCode>rai</RegistrationCode>

  <RegistrationDescription>individual</RegistrationDescription>

  <SpecialFeeProgramCode />

  <StatementDescriptorValue>Delores IRA</StatementDescriptorValue>

  <MarginFlag>false</MarginFlag>

  <HasMultipleMarginFlag>false</HasMultipleMarginFlag>

  <PledgedFlag>false</PledgedFlag>

  <IsNRAHolderFlag>false</IsNRAHolderFlag>

  <NonPurposeLoanFlag>false</NonPurposeLoanFlag>

  <NoMoreBusinessFlag>false</NoMoreBusinessFlag>

  <DiscretionaryFlag>false</DiscretionaryFlag>

  <InactiveFlag>false</InactiveFlag>

  <RestrictionFlag>false</RestrictionFlag>

  <AccountClosedFlag>false</AccountClosedFlag>

  <ThirtyDayFinalNoticeFlag>false</ThirtyDayFinalNoticeFlag>

  <AccountOpenDate>XXXXXXX</AccountOpenDate>

  <AccountStatus>Active</AccountStatus>

  <TEFRA>

  <TEFRAStatusCode>rec</TEFRAStatusCode>

  <TEFRAStatusDescription>YYYYYY</TEFRAStatusDescription>

  <TEFRAExpireDate>XXXXXXX</TEFRAExpireDate>

  </TEFRA>

  <AccountRelationship>

  <RelationshipId>sdfjkh23847</RelationshipId>

  <RelationshipName>XXXXXX</RelationshipName>

  <RelationshipTierCode>1</RelationshipTierCode>

  <PrimaryFlag>false</PrimaryFlag>

  </AccountRelationship>

  <FinancialAdvisor>

  <FANumber>dcgfdgs</FANumber>

  </FinancialAdvisor>

  <AccountAgreement>

  <DocumentCode>av1</DocumentCode>

  <DocumentDescription>Axxxxx</DocumentDescription>

  <DocumentStatusCode>nrq</DocumentStatusCode>

  <DocumentStatusDescription>xxxx</DocumentStatusDescription>

  </AccountAgreement>

  <AccountAgreement>

  <DocumentCode>xxx</DocumentCode>

  <DocumentDescription>xxx</DocumentDescription>

  <DocumentStatusCode>xxxx</DocumentStatusCode>

  <DocumentStatusDescription>xxxxx</DocumentStatusDescription>

  </AccountAgreement>

  <AccountAgreement>

  <DocumentCode>xx</DocumentCode>

  <DocumentDescription>xxxxxx</DocumentDescription>

  <DocumentStatusCode>xxx</DocumentStatusCode>

  <DocumentStatusDescription>xxxxx</DocumentStatusDescription>

  </AccountAgreement>

  <AccountAgreement>

  <DocumentCode>oas</DocumentCode>

  <DocumentDescription>xxxxx</DocumentDescription>

  <DocumentStatusCode>xxxxp</DocumentStatusCode>

  <DocumentStatusDescription>xxxx</DocumentStatusDescription>

  </AccountAgreement>

  <AccountAgreement>

  <DocumentCode>xxxx</DocumentCode>

  <DocumentDescription>xxxx</DocumentDescription>

  <DocumentStatusCode>xxx</DocumentStatusCode>

  <DocumentStatusDescription>xxxx</DocumentStatusDescription>

  </AccountAgreement>

  <AccountAgreement>

  <DocumentCode>xxx</DocumentCode>

  <DocumentDescription>xxxx</DocumentDescription>

  <DocumentStatusCode>nrq</DocumentStatusCode>

  <DocumentStatusDescription>N - Not Required</DocumentStatusDescription>

  </AccountAgreement>

  <Address>

  <Name1>xxxx</Name1>

  <Name2>xxxx</Name2>

  <Street1>xxx</Street1>

  <CityName>xxx</CityName>

  <StateCode>xx </StateCode>

  <County>xxx</County>

  <PostalCode>xxxx</PostalCode>

  <CountryCode>us </CountryCode>

  <CountryName>xxxx</CountryName>

  <InternationalFlag>false</InternationalFlag>

  <AddressTypeCode>***</AddressTypeCode>

  </Address>

  <Holder>

  <FirstName>xxxx</FirstName>

  <MiddleName>x</MiddleName>

  <LastName>xxxxx</LastName>

  <FullName>xxxx</FullName>

  <HolderType>xxx</HolderType>

  <HolderFunctionCode>xxx</HolderFunctionCode>

  <HolderFunctionDescription>xxxx</HolderFunctionDescription>

  <TaxId>xxxx</TaxId>

  <HolderId>xxxx</HolderId>

  <DateOfBirth>xxxxx</DateOfBirth>

  <EmployeeRelated>false</EmployeeRelated>

  </Holder>

  </Account>

  </AccountDetail>

  i created a temp table

  CREATE TABLE #XMLwithOpenXML

  (

  Id INT IDENTITY PRIMARY KEY,

  XMLData xml

  )

  copied the result set (XML) of a storedprocedure into #XMLwithOpenXML

  then i am using the following approach to get the values in the temp table

  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

  SELECT @XML = XMLData

  FROM #XMLwithOpenXML

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT AccountNumber,ShortName

  FROM OPENXML(@hDoc,'AccountDetail')

  WITH

  (

  AccountNumber [varchar](50) '@AccountNumber',

  ShortName [varchar](100) '@ShortName'

  )

  EXEC sp_xml_removedocument @hDoc

  GO

  but for some reason i am getting NULL as results (Expecting to get 12345678 and John)

  I am not sure where i made a mistake

  thanks

• Sarah Wagner

  Hall of Fame

  Points: 3178

  More actions

  July 21, 2014 at 2:13 pm

  #1731208

  XML is case sensitive, but that doesn't appear to be the problem. Try:

  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

  SELECT @XML = XMLData

  FROM #XMLwithOpenXML

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT AccountNumber,ShortName

  FROM OPENXML(@hDoc,'//Account')

  WITH

  (

  AccountNumber [varchar](50) '@AccountNumber',

  ShortName [varchar](100) '@ShortName'

  )

• koolme_85

  Ten Centuries

  Points: 1153

  More actions

  July 21, 2014 at 2:20 pm

  #1731212

  did try

  DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

  SELECT @XML = XMLData

  FROM #XMLwithOpenXML

  EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

  SELECT AccountNumber,ShortName

  FROM OPENXML(@hDoc,'//Account')WITH

  (

  AccountNumber [varchar](50) '@AccountNumber',

  ShortName [varchar](100) '@ShortName'

  )

  EXEC sp_xml_removedocument @hDoc

  GO

  but no luck

• Jeff Moden

  SSC Guru

  Points: 1004311

  More actions

  July 21, 2014 at 2:23 pm

  #1731214

  This is a duplicate post, which only serves to split up the answers. I recommend that no other answers be posted here. Instead, please see the original post at the following URL.

  http://www.sqlservercentral.com/Forums/Topic1594688-391-1.aspx

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)