July 21, 2014 at 9:28 pm
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
Change is inevitable... Change for the better is not.
July 21, 2014 at 11:15 pm
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.
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
July 22, 2014 at 2:32 am
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.
********************************************************************/
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
,TEFRA.DT.value('TEFRAStatusCode[1]' ,'VARCHAR(10)') AS TEFRAStatusCode
,ACCOUNTRELATIONSHIP.DT.value('RelationshipId[1]' ,'VARCHAR(10)') AS RelationshipId
,FINANCIALADVISOR.DT.value('FANumber[1]' ,'VARCHAR(10)') AS FANumber
,ADDREESS.DT.value('CountryCode[1]' ,'VARCHAR(10)') AS CountryCode
,HOLDER.DT.value('EmployeeRelated[1]' ,'BIT') AS EmployeeRelated
FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)
OUTER APPLY ACCOUNT.DT.nodes('TEFRA') AS TEFRA(DT)
OUTER APPLY ACCOUNT.DT.nodes('AccountRelationship') AS ACCOUNTRELATIONSHIP(DT)
OUTER APPLY ACCOUNT.DT.nodes('FinancialAdvisor') AS FINANCIALADVISOR(DT)
OUTER APPLY ACCOUNT.DT.nodes('Address') AS ADDREESS(DT)
OUTER APPLY ACCOUNT.DT.nodes('Holder') AS HOLDER(DT);
/********************************************************************
Part 3. Adding a little twist to the previous by counting the
instances of the AccountAgreement
********************************************************************/
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
,TEFRA.DT.value('TEFRAStatusCode[1]' ,'VARCHAR(10)') AS TEFRAStatusCode
,ACCOUNTRELATIONSHIP.DT.value('RelationshipId[1]' ,'VARCHAR(10)') AS RelationshipId
,FINANCIALADVISOR.DT.value('FANumber[1]' ,'VARCHAR(10)') AS FANumber
,ADDREESS.DT.value('CountryCode[1]' ,'VARCHAR(10)') AS CountryCode
,HOLDER.DT.value('EmployeeRelated[1]' ,'BIT') AS EmployeeRelated
,AACOUNTING.AccountAgreementCount
FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNT(DT)
OUTER APPLY ACCOUNT.DT.nodes('TEFRA') AS TEFRA(DT)
OUTER APPLY ACCOUNT.DT.nodes('AccountRelationship') AS ACCOUNTRELATIONSHIP(DT)
OUTER APPLY ACCOUNT.DT.nodes('FinancialAdvisor') AS FINANCIALADVISOR(DT)
OUTER APPLY ACCOUNT.DT.nodes('Address') AS ADDREESS(DT)
OUTER APPLY ACCOUNT.DT.nodes('Holder') AS HOLDER(DT)
OUTER APPLY
(
SELECT COUNT(*) AS AccountAgreementCount
FROM @XMLT.nodes('AccountDetail/Account') AS ACCOUNTX(DT)
OUTER APPLY ACCOUNTX.DT.nodes('AccountAgreement') AS ACCOUNTAGREEMENT(DT)
WHERE ACCOUNT.DT.value('AccountNumber[1]','VARCHAR(20)') = ACCOUNTX.DT.value('AccountNumber[1]','VARCHAR(20)')
) AS AACOUNTING
Edit: Added the code as attachment.
July 22, 2014 at 3:56 am
Jeff Moden (7/21/2014)
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. 😛
I think XML is a brilliant way of passing multi value parameters, the binary XML format is very efficient, it can hold almost any data structure, with a schema collection it can have all the constrictions and control of a relational schema, value lookup and validation etc.. Any other approach is either less capable or more difficult to implement for but the simplest of applications.
😎
July 22, 2014 at 5:51 am
good question mister.magoo
i would if i could , i do not have permissions to create or alter the procedure
July 22, 2014 at 5:59 am
i cant pivot it , the same parameters that go into the storedproc also go into a webservice which returns a response and i have to validate the storedprocedure response with services response , there is a mapping that happents in the service (between SP and response)....for that i need the output to be in a table with out pivot
July 22, 2014 at 9:22 am
Thanks for the inputs
this is what i am trytig to do
Exec stored proc
result = Col1 (XML)
now how do i capture this into a variable and follow the procedure you provided ?
here is my SP Execution method
{DECLARE@return_value int
EXEC@return_value = [dbo].[bofa_sp_gap_sel_acc]
@SearchType = N'A',
@SearchInputList = N'94378495',
@FAsInputList = null
SELECT'Return Value' = @return_value
}
sorry for asking dumb questions (i have not worked in DB for a long time now)
July 22, 2014 at 10:12 am
You can query the table directly instead of using a variable, straight forward;-) Here is the same code as before with the difference of using an XML column in a table rather than a variable.
😎
Quick questions:
a. is there only one XML in the table?
b. if there are more, do you have a method of filtering the select?
Anything else just ask!
/********************************************************************
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;
/*******************************************
Insert the value into a table
*******************************************/
DECLARE @TBLXML TABLE (COL1 XML NOT NULL);
INSERT INTO @TBLXML(COL1) VALUES (@XMLT);
/********************************************************************
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 @TBLXML X
OUTER APPLY X.COL1.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 @TBLXML X
OUTER APPLY X.COL1.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 @TBLXML X
OUTER APPLY X.COL1.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 @TBLXML X
OUTER APPLY X.COL1.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 @TBLXML X
OUTER APPLY X.COL1.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 @TBLXML X
OUTER APPLY X.COL1.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 @TBLXML X
OUTER APPLY X.COL1.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.
********************************************************************/
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
,TEFRA.DT.value('TEFRAStatusCode[1]' ,'VARCHAR(10)') AS TEFRAStatusCode
,ACCOUNTRELATIONSHIP.DT.value('RelationshipId[1]' ,'VARCHAR(10)') AS RelationshipId
,FINANCIALADVISOR.DT.value('FANumber[1]' ,'VARCHAR(10)') AS FANumber
,ADDREESS.DT.value('CountryCode[1]' ,'VARCHAR(10)') AS CountryCode
,HOLDER.DT.value('EmployeeRelated[1]' ,'BIT') AS EmployeeRelated
FROM @TBLXML X
OUTER APPLY X.COL1.nodes('AccountDetail/Account') AS ACCOUNT(DT)
OUTER APPLY ACCOUNT.DT.nodes('TEFRA') AS TEFRA(DT)
OUTER APPLY ACCOUNT.DT.nodes('AccountRelationship') AS ACCOUNTRELATIONSHIP(DT)
OUTER APPLY ACCOUNT.DT.nodes('FinancialAdvisor') AS FINANCIALADVISOR(DT)
OUTER APPLY ACCOUNT.DT.nodes('Address') AS ADDREESS(DT)
OUTER APPLY ACCOUNT.DT.nodes('Holder') AS HOLDER(DT);
/********************************************************************
Part 3. Adding a little twist to the previous by counting the
instances of the AccountAgreement
********************************************************************/
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
,TEFRA.DT.value('TEFRAStatusCode[1]' ,'VARCHAR(10)') AS TEFRAStatusCode
,ACCOUNTRELATIONSHIP.DT.value('RelationshipId[1]' ,'VARCHAR(10)') AS RelationshipId
,FINANCIALADVISOR.DT.value('FANumber[1]' ,'VARCHAR(10)') AS FANumber
,ADDREESS.DT.value('CountryCode[1]' ,'VARCHAR(10)') AS CountryCode
,HOLDER.DT.value('EmployeeRelated[1]' ,'BIT') AS EmployeeRelated
,AACOUNTING.AccountAgreementCount
FROM @TBLXML X
OUTER APPLY X.COL1.nodes('AccountDetail/Account') AS ACCOUNT(DT)
OUTER APPLY ACCOUNT.DT.nodes('TEFRA') AS TEFRA(DT)
OUTER APPLY ACCOUNT.DT.nodes('AccountRelationship') AS ACCOUNTRELATIONSHIP(DT)
OUTER APPLY ACCOUNT.DT.nodes('FinancialAdvisor') AS FINANCIALADVISOR(DT)
OUTER APPLY ACCOUNT.DT.nodes('Address') AS ADDREESS(DT)
OUTER APPLY ACCOUNT.DT.nodes('Holder') AS HOLDER(DT)
OUTER APPLY
(
SELECT COUNT(*) AS AccountAgreementCount
FROM @TBLXML X
OUTER APPLY X.COL1.nodes('AccountDetail/Account') AS ACCOUNTX(DT)
OUTER APPLY ACCOUNTX.DT.nodes('AccountAgreement') AS ACCOUNTAGREEMENT(DT)
WHERE ACCOUNT.DT.value('AccountNumber[1]','VARCHAR(20)') = ACCOUNTX.DT.value('AccountNumber[1]','VARCHAR(20)')
) AS AACOUNTING
July 22, 2014 at 10:28 am
The storedprocedure returns just 1 column and its xml
so this is what i was trying to do
create a temp table
CREATE TABLE #XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData xml
)
And then insert script
insert into #XMLwithOpenXML (XMLData)
EXEC [dbo].[rj_sp_esb_sel_acc]
@SearchType = N'A',
@SearchInputList = N'57575757',
@FAsInputList = null
ERRROR:-
Msg 6819, Level 16, State 5, Procedure bofa_sp_esb_sel_acc, Line 712
The FOR XML clause is not allowed in a INSERT statement.
I think the storedprocedure is using (FOR XML AUTO, ELEMENTS, TYPE)
July 22, 2014 at 10:31 am
DECLARE @XMLT XML = '<AccountDetail>
<Account>
<AccountNumber>12345678</AccountNumber>
<ShortName>john</ShortName>
.........................
here i need
Declare @XMLT XML = (Result XML column from storedprocedure)
i,e
Declare @XMLT XML = exec sp (xml result)
July 22, 2014 at 10:35 am
Also Tried
DECLARE @TBLXML TABLE (COL1 XML NOT NULL);
INSERT INTO @TBLXML(COL1)
EXEC [dbo].[bofa_sp_esb_sel_acc]
@SearchType = N'A',
@SearchInputList = N'57575757',
@FAsInputList = null
Error :-
Msg 6819, Level 16, State 5, Procedure rj_sp_esb_sel_acc, Line 712
The FOR XML clause is not allowed in a INSERT statement.
July 22, 2014 at 11:59 am
Evil Kraig F (7/21/2014)
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.
Agree on the "to each their own" but I wouldn't use a 2 stage splitter for this. It's a simple data transfer similar to what replication would do. What's so bad about using something like BCP to export the data to a central transfer repository and simply import the data at the other side? Rhetorical question, for sure. Just another way to skin the proverbial cat.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2014 at 2:59 pm
Jeff Moden (7/22/2014)
What's so bad about using something like BCP to export the data to a central transfer repository and simply import the data at the other side? Rhetorical question, for sure. Just another way to skin the proverbial cat.
Jeff, usually we're on the same page and see eye to eye on things, even if we prefer to do them in different ways, but in this... you have me boggled.
Why would you ever want to involve drive I/O and third point of failure (file system) in sending easily transmitted data? Besides the fact that I don't BCP without significant need (xp_cmdshell signoffs and the like), and it's normally a one-shot item based on the initial parameters (so nothing I'd want persisted), I still don't understand. For user initiated persistable requests I use SSRS, for automation and archiving I use BCP/SSIS (depends on what makes most sense, usually SSIS in my current environment). For data communications? I don't understand that.
EDIT: Also, it's not similar to replication, just offhand, not remotely close for the scenarios I have in mind. If I have 10 users sending down simultaneous requests, each one ends up with a different shipment. Scenario: Local system has the necessary columns to properly deal with half the where clause, along with say a date range to get data for. Using those IDs and ranges from the return against a foreign system's significantly reduces the data coming back from the link.
Two approaches: Either you ship the requested filtered list over to the linked server so it can use it, or the linked server has to call back to the local (thus, loop the loop coding) to get its filter. I prefer to ship first. Each user can be doing this with completely different parameters, thus different lists.
I just want to make sure we're thinking about the same scenario(s).
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
July 22, 2014 at 10:29 pm
koolme_85 (7/22/2014)
Also TriedDECLARE @TBLXML TABLE (COL1 XML NOT NULL);
INSERT INTO @TBLXML(COL1)
EXEC [dbo].[bofa_sp_esb_sel_acc]
@SearchType = N'A',
@SearchInputList = N'57575757',
@FAsInputList = null
Error :-
Msg 6819, Level 16, State 5, Procedure rj_sp_esb_sel_acc, Line 712
The FOR XML clause is not allowed in a INSERT statement.
Quick question, what is the version of sql server on the source (xml stored procedure) server? Looks to me like a solution from ca. 2000.
If the stored procedure is doing a SELECT FOR XML, the select statement inside the procedure has to be wrapped in another select in order to catch the output, something like
CREATE PROCEDURE USP_DO_XML_AUTOX (@XOUT XML OUTPUT)
AS
SELECT @XOUT = (SELECT *
FROM INFORMATION_SCHEMA.TABLES
FOR XML AUTO, ELEMENTS)
Which then is used like this
DECLARE @XO XML
EXEC dbo.USP_DO_XML_AUTO @XO OUTPUT
This works fine but since you cannot change the stored procedure, the problem is how to grab the output as SQL Server will throw an error when trying to insert/assign the output to anything.
First things coming to mind would be OPENROWSET/OPENQUERY, SSIS and then bcp but the last option involves dumping the output into a file.
😎
July 24, 2014 at 6:41 pm
Evil Kraig F (7/22/2014)
Jeff Moden (7/22/2014)
What's so bad about using something like BCP to export the data to a central transfer repository and simply import the data at the other side? Rhetorical question, for sure. Just another way to skin the proverbial cat.Jeff, usually we're on the same page and see eye to eye on things, even if we prefer to do them in different ways, but in this... you have me boggled.
Why would you ever want to involve drive I/O and third point of failure (file system) in sending easily transmitted data? Besides the fact that I don't BCP without significant need (xp_cmdshell signoffs and the like), and it's normally a one-shot item based on the initial parameters (so nothing I'd want persisted), I still don't understand. For user initiated persistable requests I use SSRS, for automation and archiving I use BCP/SSIS (depends on what makes most sense, usually SSIS in my current environment). For data communications? I don't understand that.
EDIT: Also, it's not similar to replication, just offhand, not remotely close for the scenarios I have in mind. If I have 10 users sending down simultaneous requests, each one ends up with a different shipment. Scenario: Local system has the necessary columns to properly deal with half the where clause, along with say a date range to get data for. Using those IDs and ranges from the return against a foreign system's significantly reduces the data coming back from the link.
Two approaches: Either you ship the requested filtered list over to the linked server so it can use it, or the linked server has to call back to the local (thus, loop the loop coding) to get its filter. I prefer to ship first. Each user can be doing this with completely different parameters, thus different lists.
I just want to make sure we're thinking about the same scenario(s).
You're absolutely correct. When it comes to SQL Server, we're pretty much joined at the hip and I'm thinking that you and I are actually talking about something different.
Because of the work I usually do, I tend to think in the "large batch" mode and took the original post that way. I think that's the fundamental difference between what you and I have each suggested. You're absolutely correct that sending XML in the form that the OP posted is one of the faster ways to transmit and consume multi-parameters. On the far side of this, the data could be very quickly shredded (sidebar... even if you used an XML Splitter because no concatenation would be involved). I wasn't looking at it that way. I was looking at sending a much larger volume of rows that might justify a form of "poor man's replication".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply