|
|
Rank: Newbie
Joined: 2/21/2017
Posts: 2
Location: Sydney
|
I have a xml file as below. I tried to parse it using XSLT (Extensible Stylesheet Language Transformations). However the syntax may be incorrect as I can not retrieve any values out of xml file. please refer below for my xml file and xslt template I wrote. thanks for your help.
-------below is xml file----------------------------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<DataProfile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/sqlserver/2008/DataDebugger/">
<ProfileVersion>1.0</ProfileVersion>
<DataSources>
<DtsDataSource ID="{AAE5E929-50B5-4A2B-8C9B-319A5E8DBB07}" Name="LocalHost.ers_s_stage_ADO_Connection">
<DtsConnectionManagerID>LocalHost.ers_s_stage_ADO_Connection</DtsConnectionManagerID>
</DtsDataSource>
<DtsDataSource ID="{9BEC2151-F307-4E3F-8643-705B4CA40369}" Name="Profiling_RGMS_ODF_CA_GM_INSTITUTIONS">
<DtsConnectionManagerID>Profiling_RGMS_ODF_CA_GM_INSTITUTIONS</DtsConnectionManagerID>
</DtsDataSource>
<DtsDataSource ID="{2E30D6F5-6D63-43C1-8FB3-6FEC8D3175A7}" Name="LocalHost ers_s_land">
<DtsConnectionManagerID>LocalHost ers_s_land</DtsConnectionManagerID>
</DtsDataSource>
</DataSources>
<DataProfileInput>
<ProfileMode>Exact</ProfileMode>
<Timeout>0</Timeout>
<Requests>
<ColumnNullRatioProfileRequest ID="NullRatioReq">
<DataSourceID>{AAE5E929-50B5-4A2B-8C9B-319A5E8DBB07}</DataSourceID>
<Table Schema="dbo" Table="RGMS_ODF_CA_GM_INSTITUTIONS" />
<Column IsWildCard="true" />
</ColumnNullRatioProfileRequest>
</Requests>
</DataProfileInput>
<DataProfileOutput>
<Profiles>
<ColumnNullRatioProfile ProfileRequestID="NullRatioReq" IsExact="true">
<DataSourceID>{AAE5E929-50B5-4A2B-8C9B-319A5E8DBB07}</DataSourceID>
<Table DataSource="." Database="ers_s_stage" Schema="dbo" Table="RGMS_ODF_CA_GM_INSTITUTIONS" RowCount="-1" />
<Column Name="ersIngestionDate" SqlDbType="DateTime" MaxLength="0" Precision="255" Scale="255" LCID="-1" CodePage="0" IsNullable="false" StringCompareOptions="0" />
<NullCount>0</NullCount>
</ColumnNullRatioProfile>
--Below is my xslt template----------------------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">
<xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
<xsl:template match="/">
<xsl:text>DataSource;Table;ColumnName;RowCount;NullCount</xsl:text>
<xsl:text> </xsl:text>
<xsl:for-each select="DataProfile/DataProfileOutput/Profiles/ColumnNullRatioProfile">
<xsl:text>"</xsl:text>
<xsl:value-of select="/Table/@Database"/>
<xsl:text>"</xsl:text>
<xsl:value-of select="/Table/@Table"/>
<xsl:text>"</xsl:text>
<xsl:value-of select="/Column/@Name"/>
<xsl:text>"</xsl:text>
<xsl:value-of select="/Table/@RowCount"/>
<xsl:text>"</xsl:text>
<xsl:value-of select="/Table/NullCount"/>
<xsl:text>"</xsl:text>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
|
|
Rank: Newbie
Joined: 10/28/2002
Posts: 1,283
Location: AT
|
The problem has to do with namespaces and your xpath statements
Either:
a) Remove the following namespace declaration from your xml file as in "test_a.xml", (it makes no sense if no schema file is assigned to the xml file):
Code:xmlns="http://schemas.microsoft.com/sqlserver/2008/DataDebugger/"
also amend your xslt as in "test_a.xslt"
OR
b) Assign the pertinent schema to the xml file (see test_b.xml) and amend the xslt as shown in "test_b.xslt)
P.S. As this is an XSLT question it belongs in the XSLT forum.
File Attachment(s):
 test_a.zip (2kb) downloaded 926 time(s).
test_b.zip (7kb) downloaded 949 time(s).
|
|
Rank: Newbie
Joined: 2/21/2017
Posts: 2
Location: Sydney
|
wow, it worked like magic. Thanks so much for your help.
|
|
|
guest |
