Need Help parsing through the XML

Question

Need Help parsing through the XML

Viewing 15 posts - 16 through 30 (of 32 total)

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

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

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.

I have to admit that using XML to do such a thing (and, yes, I have send data "over" for the very same reason you've suggested) would be the furthest thing from my mind except for maybe sending it as EDI. 😛

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

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

Jeff Moden (7/21/2014)
I have to admit that using XML to do such a thing (and, yes, I have send data "over" for the very same reason you've suggested) would be the furthest thing from my mind except for maybe sending it as EDI. 😛

Heh, well, to each their own, I suppose. I find it much easier (and more obvious for inheritors) than using a two stage splitter, particularly when in some cases I'm running a MAX transfer. I'm just used to it at this point, and find it relatively easy to setup.

- 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

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

First a correction, I was wrong earlier, OPENXML is NOT DEPRECIATED. My gray-cell memory playing tricks on me again, incorrectly recalled a note from Michael Coles book Pro SQL Server 2008 XML. Michael states that the OPENXML Rowset Provider is supplied as "backward-compatibility with legacy SQL Server 2000 code" and should be avoided in any new development. And since the OPENXML is based on COM, the server assigns 1/8 of it's memory to the instance regardless of the XML document size, not very efficient. Think of a server with 1TB ram, using OPENXML the instance would get 128Gb assigned to it:pinch:

Switching back to the problem, below is pretty much everything needed to properly shred the XML. I tried to comment it properly but feel free to ask if there are any questions:-P

😎

/********************************************************************

Preparation: Declare a variable to hold the XML. The XML is

implicitly casted from a VARCHAR value which means that the

server treats it as a one byte encoded document. If NVARCHAR is

used, the server treats it as UTF-16.

The XML Structure:

<AccountDetail>

<Account>

<TEFRA />

<AccountRelationship />

<FinancialAdvisor />

<AccountAgreement />

<Address />

<Holder />

</Account>

</AccountDetail>

********************************************************************/

DECLARE @XMLT 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>2010-01-20</AccountOpenDate>

<AccountStatus>Active</AccountStatus>

<TEFRA>

<TEFRAStatusCode>rec</TEFRAStatusCode>

<TEFRAStatusDescription>YYYYYY</TEFRAStatusDescription>

<TEFRAExpireDate>2015-12-31</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>1967-07-20</DateOfBirth>

<EmployeeRelated>false</EmployeeRelated>

</Holder>

</Account>

</AccountDetail>';

/********************************************************************

Display the XML, to view it, simply click on the result.

********************************************************************/

SELECT @XMLT AS TheXML;

/********************************************************************

Part 1. each element parsed separately. AccountNumber relates to

the parent element and the ROW_NUMBER function provides an

instance identifier.

********************************************************************/

/********************************************************************

Account

<AccountDetail>

<Account>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ACCOUNT.DT.value('ShortName[1]' ,'VARCHAR(50)') AS ShortName

,ACCOUNT.DT.value('CategoryCode[1]' ,'VARCHAR(10)') AS CategoryCode

,ACCOUNT.DT.value('CategoryDescription[1]' ,'VARCHAR(50)') AS CategoryDescription

,ACCOUNT.DT.value('CategorySequenceNumber[1]' ,'INT') AS CategorySequenceNumber

,ACCOUNT.DT.value('SubCategoryCode[1]' ,'VARCHAR(10)') AS SubCategoryCode

,ACCOUNT.DT.value('SubCategoryDescription[1]' ,'VARCHAR(50)') AS SubCategoryDescription

,ACCOUNT.DT.value('ClassCode[1]' ,'VARCHAR(10)') AS ClassCode

,ACCOUNT.DT.value('RegistrationCode[1]' ,'VARCHAR(10)') AS RegistrationCode

,ACCOUNT.DT.value('RegistrationDescription[1]' ,'VARCHAR(50)') AS RegistrationDescription

,ACCOUNT.DT.value('SpecialFeeProgramCode[1]' ,'VARCHAR(10)') AS SpecialFeeProgramCode

,ACCOUNT.DT.value('StatementDescriptorValue[1]' ,'VARCHAR(50)') AS StatementDescriptorValue

,ACCOUNT.DT.value('MarginFlag[1]' ,'BIT') AS MarginFlag

,ACCOUNT.DT.value('HasMultipleMarginFlag[1]' ,'BIT') AS HasMultipleMarginFlag

,ACCOUNT.DT.value('PledgedFlag[1]' ,'BIT') AS PledgedFlag

,ACCOUNT.DT.value('IsNRAHolderFlag[1]' ,'BIT') AS IsNRAHolderFlag

,ACCOUNT.DT.value('NonPurposeLoanFlag[1]' ,'BIT') AS NonPurposeLoanFlag

,ACCOUNT.DT.value('NoMoreBusinessFlag[1]' ,'BIT') AS NoMoreBusinessFlag

,ACCOUNT.DT.value('DiscretionaryFlag[1]' ,'BIT') AS DiscretionaryFlag

,ACCOUNT.DT.value('InactiveFlag[1]' ,'BIT') AS InactiveFlag

,ACCOUNT.DT.value('RestrictionFlag[1]' ,'BIT') AS RestrictionFlag

,ACCOUNT.DT.value('AccountClosedFlag[1]' ,'BIT') AS AccountClosedFlag

,ACCOUNT.DT.value('ThirtyDayFinalNoticeFlag[1]' ,'BIT') AS ThirtyDayFinalNoticeFlag

,ACCOUNT.DT.value('AccountOpenDate[1]' ,'DATETIME') AS AccountOpenDate

,ACCOUNT.DT.value('AccountStatus[1]' ,'VARCHAR(10)') AS AccountStatus

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT);

/********************************************************************

Account - TEFRA

<AccountDetail>

<Account>

<TEFRA>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS TEFRA_RID

,TEFRA.DT.value('TEFRAStatusCode[1]' ,'VARCHAR(10)') AS TEFRAStatusCode

,TEFRA.DT.value('TEFRAStatusDescription[1]' ,'VARCHAR(50)') AS TEFRAStatusDescription

,TEFRA.DT.value('TEFRAExpireDate[1]' ,'DATETIME') AS TEFRAExpireDate

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)

OUTER APPLY ACCOUNT.DT.nodes('TEFRA') AS TEFRA(DT);

/********************************************************************

Account - AccountRelationship

<AccountDetail>

<Account>

<AccountRelationship>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS AccountRelationship_RID

,ACCOUNTRELATIONSHIP.DT.value('RelationshipId[1]' ,'VARCHAR(10)') AS RelationshipId

,ACCOUNTRELATIONSHIP.DT.value('RelationshipName[1]' ,'VARCHAR(50)') AS RelationshipName

,ACCOUNTRELATIONSHIP.DT.value('RelationshipTierCode[1]' ,'VARCHAR(10)') AS RelationshipTierCode

,ACCOUNTRELATIONSHIP.DT.value('PrimaryFlag[1]' ,'BIT') AS PrimaryFlag

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)

OUTER APPLY ACCOUNT.DT.nodes('AccountRelationship') AS ACCOUNTRELATIONSHIP(DT);

/********************************************************************

Account - FinancialAdvisor

<AccountDetail>

<Account>

<FinancialAdvisor>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS FinancialAdvisor_RID

,FINANCIALADVISOR.DT.value('FANumber[1]' ,'VARCHAR(10)') AS FANumber

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)

OUTER APPLY ACCOUNT.DT.nodes('FinancialAdvisor') AS FINANCIALADVISOR(DT);

/********************************************************************

Account - AccountAgreement

<AccountDetail>

<Account>

<AccountAgreement>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS AccountAgreement_RID

,ACCOUNTAGREEMENT.DT.value('DocumentCode[1]' ,'VARCHAR(10)') AS DocumentCode

,ACCOUNTAGREEMENT.DT.value('DocumentDescription[1]' ,'VARCHAR(50)') AS DocumentDescription

,ACCOUNTAGREEMENT.DT.value('DocumentStatusCode[1]' ,'VARCHAR(10)') AS DocumentStatusCode

,ACCOUNTAGREEMENT.DT.value('DocumentStatusDescription[1]' ,'VARCHAR(50)') AS DocumentStatusDescription

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)

OUTER APPLY ACCOUNT.DT.nodes('AccountAgreement') AS ACCOUNTAGREEMENT(DT);

/********************************************************************

Account - Address

<AccountDetail>

<Account>

<Address>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Address_RID

,ADDREESS.DT.value('Name1[1]' ,'VARCHAR(50)') AS Name1

,ADDREESS.DT.value('Name2[1]' ,'VARCHAR(50)') AS Name2

,ADDREESS.DT.value('Street1[1]' ,'VARCHAR(50)') AS Street1

,ADDREESS.DT.value('CityName[1]' ,'VARCHAR(50)') AS CityName

,ADDREESS.DT.value('StateCode[1]' ,'VARCHAR(10)') AS StateCode

,ADDREESS.DT.value('County[1]' ,'VARCHAR(50)') AS County

,ADDREESS.DT.value('PostalCode[1]' ,'VARCHAR(10)') AS PostalCode

,ADDREESS.DT.value('CountryCode[1]' ,'VARCHAR(10)') AS CountryCode

,ADDREESS.DT.value('CountryName[1]' ,'VARCHAR(50)') AS CountryName

,ADDREESS.DT.value('InternationalFlag[1]' ,'BIT') AS InternationalFlag

,ADDREESS.DT.value('AddressTypeCode[1]' ,'VARCHAR(10)') AS AddressTypeCode

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)

OUTER APPLY ACCOUNT.DT.nodes('Address') AS ADDREESS(DT);

/********************************************************************

Account - Holder

<AccountDetail>

<Account>

<Holder>

********************************************************************/

SELECT

ACCOUNT.DT.value('AccountNumber[1]' ,'VARCHAR(20)') AS AccountNumber

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Holder_RID

,HOLDER.DT.value('FirstName[1]' ,'VARCHAR(50)') AS FirstName

,HOLDER.DT.value('MiddleName[1]' ,'VARCHAR(50)') AS MiddleName

,HOLDER.DT.value('FullName[1]' ,'VARCHAR(100)') AS FullName

,HOLDER.DT.value('HolderType[1]' ,'VARCHAR(50)') AS HolderType

,HOLDER.DT.value('HolderFunctionCode[1]' ,'VARCHAR(50)') AS HolderFunctionCode

,HOLDER.DT.value('HolderFunctionDescription[1]' ,'VARCHAR(100)') AS HolderFunctionDescription

,HOLDER.DT.value('TaxId[1]' ,'VARCHAR(10)') AS TaxId

,HOLDER.DT.value('HolderId[1]' ,'VARCHAR(10)') AS HolderId

,HOLDER.DT.value('DateOfBirth[1]' ,'DATETIME') AS DateOfBirth

,HOLDER.DT.value('EmployeeRelated[1]' ,'BIT') AS EmployeeRelated

FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)

OUTER APPLY ACCOUNT.DT.nodes('Holder') AS HOLDER(DT);

/********************************************************************

Part 2. Query the parent and more than one child elements

Using the singular queries as building blocks, just like

LEGO.

In the sample data, the AccountAgreement has multiple

instances and is therefore left out of this sample.