Need Help parsing through the XML

  • 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

  • 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.

    😎

  • 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 (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.

    😎

  • 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

  • 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

    😎

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

  • 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.

  • 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

  • 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
  • [/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]

  • 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

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

  • 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]

  • 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

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

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

  • Viewing 15 posts - 1 through 15 (of 32 total)

    You must be logged in to reply to this topic. Login to reply