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 1234»»»

Need Help parsing through the XML Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 10:53 AM
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 an XML that needs to be written onto a table
here is what i have done

i created a table
CREATE TABLE #XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData xml

)

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

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>


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 #1594688
Posted Monday, July 21, 2014 12:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 2,214, Visits: 5,984
Quick suggestion, don't use OPENXML, it is a depreciated feature and shoul not be used in new development. It is also much slower than XQuery/nodes methods, there are many examples on the XML forum.
Post #1594754
Posted Monday, July 21, 2014 12:25 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:59 AM
Points: 20,734, Visits: 32,506
Eirikur Eiriksson (7/21/2014)
Quick suggestion, don't use OPENXML, it is a depreciated feature and shoul not be used in new development. It is also much slower than XQuery/nodes methods, there are many examples on the XML forum.


Did not know that. That's how I figured out how to parse an XML file I used for a current project. I was having too much difficulty getting XQuery to work and was on a tight timeline. Had to use what worked to get it done.

Now moving on to the next phase where I am learning to use Power Shell to do the same thing I just finished doing in T-SQL.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1594765
Posted Monday, July 21, 2014 12:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 2,214, Visits: 5,984
Lynn Pettis (7/21/2014)
Eirikur Eiriksson (7/21/2014)
Quick suggestion, don't use OPENXML, it is a depreciated feature and shoul not be used in new development. It is also much slower than XQuery/nodes methods, there are many examples on the XML forum.


Did not know that. That's how I figured out how to parse an XML file I used for a current project. I was having too much difficulty getting XQuery to work and was on a tight timeline. Had to use what worked to get it done.

Now moving on to the next phase where I am learning to use Power Shell to do the same thing I just finished doing in T-SQL.


Pop over a sample, I'll peace together my most efficient XQuery/nodes approach. Gives me something to do on the commute.
Post #1594777
Posted Monday, July 21, 2014 1:37 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
Thanks for the response Guys , i guess i was posting in an inappropriate thread.
i did try other methods but no luck
i guess i shall try posting in the XML forum
Post #1594804
Posted Monday, July 21, 2014 1:58 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 2,214, Visits: 5,984
koolme_85 (7/21/2014)
Thanks for the response Guys , i guess i was posting in an inappropriate thread.
i did try other methods but no luck
i guess i shall try posting in the XML forum

I'll post an example tomorrow morning
Post #1594813
Posted Monday, July 21, 2014 2:14 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
Eirikur Eiriksson (7/21/2014)
Quick suggestion, don't use OPENXML, it is a depreciated feature and shoul not be used in new development. It is also much slower than XQuery/nodes methods, there are many examples on the XML forum.


It's a real shame if that's true. I've found that the "edge" table (an adjacency list by any other term) that it made behind the scenes was actually pretty useful in setting up some forms of "auto interrogation" where you didn't actually need to know anything about the XML to "flatten" it out or even split it into sub table result sets. IMHO, it was the only saving grace for anything having to do with XML.


--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 #1594822
Posted Monday, July 21, 2014 2:30 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
Eirikur Eiriksson (7/21/2014)
Quick suggestion, don't use OPENXML, it is a depreciated feature and shoul not be used in new development. It is also much slower than XQuery/nodes methods, there are many examples on the XML forum.


Where is it that openXML is deprecated? http://msdn.microsoft.com/en-us/library/ms143729.aspx Just curious because I don't see it. If I missed it, yikes.
Post #1594830
Posted Monday, July 21, 2014 2:33 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
thanks Eirikur Eiriksson

here is what i am looking for

my Storedprocedure returns an XML

Stored procedure

EXEC [dbo].[bofa_sp_esb_sel_acc]
@SearchType = N'A',
@SearchInputList = N'57575757',
@FAsInputList = null

Result is an xml

<AccountDetail>
<Account>
<AccountNumber>57575757</AccountNumber>
<ShortName>Grammer IRA</ShortName>
<CategoryCode>ret</CategoryCode>
<CategoryDescription>Retirement Account - RJ Custodian</CategoryDescription>
<CategorySequenceNumber>1</CategorySequenceNumber>


<Holder>
<FirstName>Delores</FirstName>
<MiddleName>M</MiddleName>
<LastName>Grammer</LastName>

</Holder>
</Account>
</AccountDetail>

i need to copy this storedproc results into temp table
and then convert the above xml into table

example
(Col1)accountnumber (Col2) shortname ........& so on and so forth
57575757, Grammer IRA ........& so on and so forth
Post #1594834
Posted Monday, July 21, 2014 5:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,796, Visits: 5,799
So is that right? You have a stored procedure that produces XML from data stored in tables and now you want to shred that XML to store the data in table(s)?

The question does arise: Why not just pop that data into the table in the first place?


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1594887
    « Prev Topic | Next Topic »

    Add to briefcase 1234»»»

    Permissions Expand / Collapse