Shredding XML column returns NULL values but correct record count!

Question

Shredding XML column returns NULL values but correct record count!

Eschergoedel

Old Hand

Points: 348
More actions
March 7, 2016 at 6:12 pm

#316701

Hi:
Using the attached xml (schema and instance document in one file), loading with Bulk Insert and Openrowset to a XML column, and applying the below, I get the correct record count but I don't get values, only NULLs. If you could help, that would be just great. Thank you very much, EG
USE [XMLDB]
GO
DECLARE @DData as XML, @pathHandle as INT
SELECT @DData = XMLData FROM [XMLDB].[dbo].[XMLTable]
EXEC sp_xml_preparedocument @pathHandle OUTPUT, @DData
;WITH XMLNAMESPACES('http://www.dummy.com/Export' AS MD,
DEFAULT ‘http://www.dummy.com/Export')
SELECT
GroupData.value('@AccountNumber','VARCHAR(50)') AS AccountNumber
,GroupData.value('@AccountType', 'VARCHAR(50)') AS AccountType
FROM [XMLDB].[dbo].[XMLTable]
CROSS APPLY @DData.nodes('MD:Document') AS XD(Document)
CROSS APPLY document.nodes('PortfolioOrGroup') AS XD2(PortfolioOrGroup)
CROSS APPLY PortfolioOrGroup.nodes('Holdings') AS XD3(Holdings)
CROSS APPLY Holdings.nodes('GroupData') AS XD4(GroupData)
CROSS APPLY GroupData.nodes('AccountNumber') AS XD4(AccountNumber)
EXEC sp_xml_removedocument @pathHandle

Viewing 8 posts - 1 through 8 (of 8 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The namespace doesn't seem to be associated to the root node - so if you can - add another dummy namespace for the outer Document node.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

This should get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = '<Document>

<xs:schema elementFormDefault="qualified" targetNamespace="http://www.dummy.com/Export" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:mstns="http://www.dummy.com/Export" xmlns="http://www.dummy.com/Export" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element msdata:IsDataSet="true" name="Document">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element minOccurs="1" maxOccurs="1" name="AsOfDate" type="xs:string" />

<xs:element minOccurs="1" maxOccurs="1" name="PriceDate" type="xs:string" />

<xs:element minOccurs="1" maxOccurs="1" name="DataSetName" type="xs:string" />

<xs:element name="PortfolioOrGroup">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element minOccurs="0" maxOccurs="unbounded" name="Holdings">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="unbounded" name="GroupData">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="1" name="GroupOrPortfolioID" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="GroupOrPortfolio" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="BillingAccountNumber" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ModelID" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ModelName" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Objective" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AccountNumber" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AccountType" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Description" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TotalCashBalance" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TotalValue" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="PerformanceInception" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Notes" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TaxID" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Advisor" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Target" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="unbounded" name="PortfolioData">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="1" name="PortfolioID" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Advisor" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Taxable" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TaxID" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="FedTaxRate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="FedTaxRateQualified" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="StateTaxRate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Objective" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AccountNumber" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AccountType" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Description" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MasterAccount" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MinCashBalance" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Discretionary" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="BirthDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ResidenceState" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="CustodianAccount" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="EquityLotSize" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MutualFundLotSize" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="FixedIncomeLotSize" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="RoundingInstructions" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="BrokerDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="EquitySellMethod" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MutualFundSellMethod" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SweepAccount" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMAAccount" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMADescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMAExtendedDescription1" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMAExtendedDescription2" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMAAssetClassCode" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMASectorCodeName" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMASubsectorCodeName" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMAAssetClassDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMASectorCodeDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SMASubsectorCodeDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ClosedAccount" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="unbounded" name="Securities">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="1" name="AssetClassCode" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AssetClassDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Factor" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Price" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="CUSIP" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ExpirationDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AnnualIncomeRate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SecurityState" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MaturityDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="RedemptionDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="RedemptionPrice" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SectorCodeName" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SectorCodeDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Notes" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Description" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SecurityType" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SharesPerContract" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="FedTaxable" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="StateTaxable" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SubSectorCodeName" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SubSectorCodeDescription" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Symbol" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="IncomeFrequency" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="BondStatus" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SandPRating" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MoodyRating" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="YieldToMaturityMarket" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Duration" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="unbounded" name="Positions">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="1" name="Symbol" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="CostBasis" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MarketValue" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TradeDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Quantity" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Weight" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TotalValue" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="AccruedIncome" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ExcludeFromPerformance" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ExcludeFromBilling" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="unbounded" name="TradeLots">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="1" name="CostBasis" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="InterfaceAccount" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Location" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="MarketValue" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="OriginalTradeDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="UnrealizedGainLossPercent" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="ReinvestIncomeGains" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TradeLotID" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Quantity" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="SettlementDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="TradeDate" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="CostPerUnit" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="UnrealizedGainLoss" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Weight" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

<AsOfDate>3/3/2016</AsOfDate>

<PriceDate>3/3/2016</PriceDate>

<DataSetName>Dummy</DataSetName>

<PortfolioOrGroup>

<Holdings xmlns="http://www.dummy.com/Export">

<GroupData>

<GroupOrPortfolioID>31665</GroupOrPortfolioID>

<GroupOrPortfolio>Portfolio</GroupOrPortfolio>

<BillingAccountNumber />

<ModelID>0</ModelID>

<ModelName />

<Objective>SUSIEQ</Objective>

<AccountNumber>112345678</AccountNumber>

<AccountType>CO</AccountType>

<Description>Doe,Jam</Description>

<TotalCashBalance>10000.00</TotalCashBalance>

<TotalValue>1000000</TotalValue>

<PerformanceInception>03/03/2015</PerformanceInception>

<Notes />

<TaxID />

<Advisor>DUMMY</Advisor>

<Target />

<PortfolioData>

<PortfolioID>12345</PortfolioID>

<Advisor>DUMMY</Advisor>

<Taxable>0</Taxable>

<TaxID />

<FedTaxRate>0</FedTaxRate>

<FedTaxRateQualified>15</FedTaxRateQualified>

<StateTaxRate>0</StateTaxRate>

<Objective>SUSIEQ</Objective>

<AccountNumber>14619749</AccountNumber>

<AccountType>CO</AccountType>

<Description>Doe, Jam</Description>

<MasterAccount>999995555</MasterAccount>

<MinCashBalance>0</MinCashBalance>

<Discretionary>-1</Discretionary>

<BirthDate />

<ResidenceState />

<CustodianAccount>XYZ</CustodianAccount>

<EquityLotSize>0</EquityLotSize>

<MutualFundLotSize>0</MutualFundLotSize>

<FixedIncomeLotSize>0</FixedIncomeLotSize>

<RoundingInstructions>No Rounding</RoundingInstructions>

<BrokerDescription>XYZ</BrokerDescription>

<EquitySellMethod>First In First Out</EquitySellMethod>

<MutualFundSellMethod>First In First Out</MutualFundSellMethod>

<SweepAccount>ZZZZZ</SweepAccount>

<SMAAccount>0</SMAAccount>

<SMADescription>DUMMY TEXT</SMADescription>

<SMAExtendedDescription1 />

<SMAExtendedDescription2 />

<SMAAssetClassCode />

<SMASectorCodeName />

<SMASubsectorCodeName />

<SMAAssetClassDescription />

<SMASectorCodeDescription />

<SMASubsectorCodeDescription />

<ClosedAccount>0</ClosedAccount>

<Securities>

<AssetClassCode>CASH</AssetClassCode>

<AssetClassDescription>Cash & Equivalents</AssetClassDescription>

<Price>0</Price>

<CUSIP>ZZZZ890000</CUSIP>

<AnnualIncomeRate>0.06</AnnualIncomeRate>

<SecurityState />

<MaturityDate />

<SectorCodeName />

<SectorCodeDescription />

<Notes>Fee Prohibited Security</Notes>

<Description>DUMMY FUND</Description>

<SecurityType>Cash</SecurityType>

<SharesPerContract>0</SharesPerContract>

<FedTaxable>True</FedTaxable>

<StateTaxable>True</StateTaxable>

<SubSectorCodeName />

<SubSectorCodeDescription />

<Symbol>XXXXX</Symbol>

<IncomeFrequency>Monthly</IncomeFrequency>

<BondStatus />

<SandPRating />

<MoodyRating />

<Positions>

<Symbol>XXXXXX</Symbol>

<CostBasis>10000</CostBasis>

<MarketValue>10000</MarketValue>

<TradeDate>03/04/2015</TradeDate>

<Quantity>100000</Quantity>

<Weight>2.955784182</Weight>

<TotalValue>1000000</TotalValue>

<AccruedIncome>0</AccruedIncome>

<ExcludeFromPerformance>0</ExcludeFromPerformance>

<ExcludeFromBilling>0</ExcludeFromBilling>

<TradeLots>

<CostBasis>10000</CostBasis>

<InterfaceAccount>987654321</InterfaceAccount>

<Location />

<MarketValue>10000.00</MarketValue>

<OriginalTradeDate>03/04/2015</OriginalTradeDate>

<UnrealizedGainLossPercent>0</UnrealizedGainLossPercent>

<ReinvestIncomeGains>2.48</ReinvestIncomeGains>

<TradeLotID>0</TradeLotID>

<Quantity>10000.00</Quantity>

<SettlementDate>03/02/2016</SettlementDate>

<TradeDate>03/04/2015</TradeDate>

<CostPerUnit>1</CostPerUnit>

<UnrealizedGainLoss>0</UnrealizedGainLoss>

<Weight>2.955784182</Weight>

</TradeLots>

</Positions>

</Securities>

</PortfolioData>

</GroupData>

</Holdings>

</PortfolioOrGroup>

</Document>';

;WITH XMLNAMESPACES(DEFAULT 'http://www.dummy.com/Export')

SELECT

DOCPOG.DATA.query('.')

FROM @TXML.nodes('*:Document/*:PortfolioOrGroup/Holdings/GroupData') AS DOCPOG(DATA)

;

Eschergoedel Old Hand Points: 348 More actions · Answer 3

Hi: thank you very much for your time and advice, much appreciated. The schema and XML instance are an XML column value. I think you are saying I should extract that to an XML variable? Do I need to build a schema collection? And if so, how can I do this? I will need to find a dynamic solution as the schema may change out of my control. What confuses me is that the schema and instance are in the same Variable value. I would appreciate it to learn what is the best practice dealing with this kind of schema/instance presentation. Thank you very much for your time. EG

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Inline schema tend to refer back to legacy patterns using DTD: the XML defined the content of the document whereas the inline schema defined the layout and formatting. In most cases if you are dealing with a fairly modern system you do NOT need to have the inline schema definition (most systems which understand XML will tend to ignore the inline schema content unless you specifically tell it to use it). Especially if you're getting a standard XML following the same structure in each transaction- having the schema sent inline just tends to bloat the message for not much value.

The issue I brought up and Erikur solved is that once you START using namespace references in SQLXML, most elements will REQUIRE some type of namespace alias in order to be found. The wildcard trick is one I wasn't aware of and would work for the example here.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Eschergoedel Old Hand Points: 348 More actions · Answer 5

I can't thank you enough for your help, both of you. The term inline schema opened up a lot of effective googling and thus learning for me. Thank you very much for your kindness in teaching me! EG

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 6

Eschergoedel (3/8/2016)
I can't thank you enough for your help, both of you. The term inline schema opened up a lot of effective googling and thus learning for me. Thank you very much for your kindness in teaching me! EG

You are very welcome.

😎

For completeness, here is a slightly more elaborate example of reading and parsing the XML from a table.

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_TEST_XML') IS NOT NULL DROP TABLE dbo.TBL_TEST_XML;

CREATE TABLE dbo.TBL_TEST_XML

(

TX_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_XML_TX_ID PRIMARY KEY CLUSTERED

,TX_XML XML NOT NULL

);