August 5, 2015 at 5:18 am
How can I extract all the attributes using nodes/value functions at the same time without using singleton.[1] which only gives me 1 value at a time.
<worksheet name="All itemss Totals">
<layout-options>
<title>
<formatted-text>
<run fontalignment="2" fontcolor="#ffffff">finance</run>
</formatted-text>
</title>
</layout-options>
<repository-location derived-from="www.xyz.com/test" id="AllitemssTotals" path="/workbooks/SubitemsActivity" revision="" />
<table>
<datasources>
<datasource caption="Allitemss" name="sqlserver.42074.554624143515" />
<datasource caption="yyz_abc (2)" name="sqlserver.42061.655586261571 (copy)" />
</datasources>
<datasource-dependencies datasource="sqlserver.42061.655586261571 (copy)">
<column caption="abc/yyz (Stickiness)" datatype="real" default-format="p0%" name="[Calculation_2840620134509667]" role="measure" type="quantitative">
<calculation class="tableau" formula="sum([abc_people])/sum([yyz_people])" scope-isolation="false" />
</column>
<column caption="abc people Count" datatype="integer" datatype-customized="true" name="[abc_people]" role="measure" type="quantitative" />
<column caption="yyz people Count" datatype="real" name="[yyz_people]" role="measure" type="quantitative" />
<column-instance column="[abc_people]" derivation="Sum" name="[sum:abc_people:qk]" pivot="key" type="quantitative" />
<column-instance column="[yyz_people]" derivation="Sum" name="[sum:yyz_people:qk]" pivot="key" type="quantitative" />
<column-instance column="[Calculation_2840620134509667]" derivation="User" name="[usr:Calculation_2840620134509667:qk]" pivot="key" type="quantitative" />
</datasource-dependencies>
<datasource-dependencies datasource="sqlserver.42074.554624143515">
<column caption="% Blacklisted" datatype="real" default-format="p0.00%" name="[% Attrition (copy)]" role="measure" type="quantitative">
<calculation class="tableau" formula="if sum([items_size_month_start]) > 0 then sum([number_of_unique_blacklist_attritions]) / sum([items_size_month_end]) end" scope-isolation="false" />
</column>
</datasource-dependencies>
</table>
</worksheet>
I need to extract all of these in one single query without cross apply so that I can relate this information in SQL table.
worksheet name,
data source captions and names
datasource-dependencies/datasource
datasource-dependencies/column caption
August 5, 2015 at 6:19 am
Quick example, should be enough to get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = N'<worksheet name="All itemss Totals">
<layout-options>
<title>
<formatted-text>
<run fontalignment="2" fontcolor="#ffffff">finance</run>
</formatted-text>
</title>
</layout-options>
<repository-location derived-from="www.xyz.com/test" id="AllitemssTotals" path="/workbooks/SubitemsActivity" revision="" />
<table>
<view>
<datasources>
<datasource caption="Allitemss" name="sqlserver.42074.554624143515" />
<datasource caption="yyz_abc (2)" name="sqlserver.42061.655586261571 (copy)" />
</datasources>
<datasource-dependencies datasource="sqlserver.42061.655586261571 (copy)">
<column caption="abc/yyz (Stickiness)" datatype="real" default-format="p0%" name="[Calculation_2840620134509667]" role="measure" type="quantitative">
<calculation class="tableau" formula="sum([abc_people])/sum([yyz_people])" scope-isolation="false" />
</column>
<column caption="abc people Count" datatype="integer" datatype-customized="true" name="[abc_people]" role="measure" type="quantitative" />
<column caption="yyz people Count" datatype="real" name="[yyz_people]" role="measure" type="quantitative" />
<column-instance column="[abc_people]" derivation="Sum" name="[sum:abc_people:qk]" pivot="key" type="quantitative" />
<column-instance column="[yyz_people]" derivation="Sum" name="[sum:yyz_people:qk]" pivot="key" type="quantitative" />
<column-instance column="[Calculation_2840620134509667]" derivation="User" name="[usr:Calculation_2840620134509667:qk]" pivot="key" type="quantitative" />
</datasource-dependencies>
<datasource-dependencies datasource="sqlserver.42074.554624143515">
<column caption="% Blacklisted" datatype="real" default-format="p0.00%" name="[% Attrition (copy)]" role="measure" type="quantitative">
<calculation class="tableau" formula="if sum([items_size_month_start]) > 0 then sum([number_of_unique_blacklist_attritions]) / sum([items_size_month_end]) end" scope-isolation="false" /></column>
</datasource-dependencies>
<rows total="true">([sqlserver.42074.554624143515].[none:country_name:nk] / [sqlserver.42074.554624143515].[:Measure Names])</rows>
<cols>([sqlserver.42074.554624143515].[yr:join_month:ok] / ([sqlserver.42074.554624143515].[mn:join_month:ok] / [sqlserver.42074.554624143515].[none:sub_items_name:nk]))</cols>
</view>
</table>
</worksheet>';
;WITH BASE_DATA AS
(
SELECT
ATT.DATA.value('local-name(.)','VARCHAR(30)') AS ATTRIB_NAME
,ATT.DATA.value('local-name(../.)','VARCHAR(30)') AS ATTRIB_PARENT_NAME
,ATT.DATA.value('.','VARCHAR(MAX)') AS ATTRIB_VALUE
FROM @TXML.nodes('//*/@*') AS ATT(DATA)
)
SELECT
BD.ATTRIB_NAME
,BD.ATTRIB_PARENT_NAME
,BD.ATTRIB_VALUE
FROM BASE_DATA BD;
Results
ATTRIB_NAME ATTRIB_PARENT_NAME ATTRIB_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------
name worksheet All itemss Totals
fontalignment run 2
fontcolor run #ffffff
derived-from repository-location http://www.xyz.com/test
id repository-location AllitemssTotals
path repository-location /workbooks/SubitemsActivity
revision repository-location
caption datasource Allitemss
name datasource sqlserver.42074.554624143515
caption datasource yyz_abc (2)
name datasource sqlserver.42061.655586261571 (copy)
datasource datasource-dependencies sqlserver.42061.655586261571 (copy)
caption column abc/yyz (Stickiness)
datatype column real
default-format column p0%
name column [Calculation_2840620134509667]
role column measure
type column quantitative
class calculation tableau
formula calculation sum([abc_people])/sum([yyz_people])
scope-isolation calculation false
caption column abc people Count
datatype column integer
datatype-customized column true
name column [abc_people]
role column measure
type column quantitative
caption column yyz people Count
datatype column real
name column [yyz_people]
role column measure
type column quantitative
column column-instance [abc_people]
derivation column-instance Sum
name column-instance [sum:abc_people:qk]
pivot column-instance key
type column-instance quantitative
column column-instance [yyz_people]
derivation column-instance Sum
name column-instance [sum:yyz_people:qk]
pivot column-instance key
type column-instance quantitative
column column-instance [Calculation_2840620134509667]
derivation column-instance User
name column-instance [usr:Calculation_2840620134509667:qk]
pivot column-instance key
type column-instance quantitative
datasource datasource-dependencies sqlserver.42074.554624143515
caption column % Blacklisted
datatype column real
default-format column p0.00%
name column [% Attrition (copy)]
role column measure
type column quantitative
class calculation tableau
formula calculation if sum([items_size_month_start]) > 0 then sum([number_of_unique_blacklist_attritions]) / sum([items_size_month_end]) end
scope-isolation calculation false
total rows true
August 5, 2015 at 7:12 am
create table #emp (id int, name varchar(64), xml_data xml)
insert into #temp values
(1, 'one', '<data><info x="42" y="99">vignesh</info></data>'),
(2, 'two', '<data><info x="57" y="72">ram</info></data>'),
(3, 'three', '<data><info x="36" y="81">karthik</info></data>'),
(4, 'four', '<data><info x="22" y="67">sekar</info></data>')
August 5, 2015 at 11:54 am
Thank you so much this was very helpful
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply