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

need help parsing XML Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 1:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:22 AM
Points: 82, Visits: 192
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
Post #1594812
Posted Monday, July 21, 2014 2:13 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 1,328, Visits: 620
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'

)
Post #1594821
Posted Monday, July 21, 2014 2:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:22 AM
Points: 82, Visits: 192
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
Post #1594826
Posted Monday, July 21, 2014 2:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse