Need Help parsing through the XML

Question

Need Help parsing through the XML

koolme_85

Ten Centuries

Points: 1153
More actions
July 21, 2014 at 10:53 am

#309268

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
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

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.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 3

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.

😎

koolme_85 Ten Centuries Points: 1153 More actions · Answer 4

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 SSC Guru Points: 182916 More actions · Answer 5

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

😎

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

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.

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)

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 7

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.

koolme_85 Ten Centuries Points: 1153 More actions · Answer 8

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

<ShortName>Grammer IRA</ShortName>

<CategoryDescription>Retirement Account - RJ Custodian</CategoryDescription>

<FirstName>Delores</FirstName>

<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 SSC-Forever Points: 47174 More actions · Answer 9

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);

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

mister.magoo (7/21/2014)
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?

I do this pretty regularly Magoo. It saves me circular trips between linked servers to pass over a reference or restriction table in XML, shred it on the far side, let it use it as a JOIN predicate list, and then get a restricted set of results back.

If I could use the table parameter between linked servers, I wouldn't have to jump the hoops.

Side Note: I'll wait for Eirikur to come back on this, my work with .nodes isn't that spectacular. I'm more familiar with OPENXML methods.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

Evil Kraig F (7/21/2014)
mister.magoo (7/21/2014)
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?
I do this pretty regularly Magoo. It saves me circular trips between linked servers to pass over a reference or restriction table in XML, shred it on the far side, let it use it as a JOIN predicate list, and then get a restricted set of results back.
If I could use the table parameter between linked servers, I wouldn't have to jump the hoops.
Side Note: I'll wait for Eirikur to come back on this, my work with .nodes isn't that spectacular. I'm more familiar with OPENXML methods.

Sure Craig, that sounds reasonable, but is that what is happening here?

MM

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

mister.magoo (7/21/2014)
Sure Craig, that sounds reasonable, but is that what is happening here?

I usually assume things like this are simplified versions of much larger concerns. Not everyone's comfortable posting their source code and info and for things like this, I usually simplify my questions down to the tightest way possible to ask it.

A number of folks here otherwise get lost in my formatting, camelcase, or if something starts with tbl instead of concentrating on my specific need for that assistance request otherwise. I figure others have noticed the pattern, thus, a simplified model of the confusion. Though, that may just be me.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

Sarah Wagner (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.
😎
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.

Agreed. I don't see it there or in the following like MS would normally do...

http://msdn.microsoft.com/en-us/library/ms186918.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)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 14

koolme_85 (7/21/2014)
i need to copy this storedproc results into temp table
and then convert the above xml into table

Ok... I realize that some folks in this thread have come up with reasons for doing that but that seems to be a little bit crazy to me. It's pretty easy to "pivot" data for multiple documents if necessary and it can certainly be done without XML.

Why do you need to do this? Is it just for a "pivot"?

--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)