SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help parsing through the XML


Need Help parsing through the XML

Author
Message
koolme_85
koolme_85
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 204
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39814 Visits: 19443
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.
Cool
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93041 Visits: 38955
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.
Cool


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.

Cool
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)
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39814 Visits: 19443
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.
Cool


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.
Cool
koolme_85
koolme_85
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 204
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39814 Visits: 19443
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
Cool
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210739 Visits: 41973
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.
Cool


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sarah Wagner
Sarah Wagner
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1862 Visits: 715
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.
Cool


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.
koolme_85
koolme_85
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 204
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
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10381 Visits: 7891
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search