I am trying to import a legacy configuration file into SQL Server table that is in XML that also has an XSLT transform file. I have imported the XML config using .nodes and XPath syntax with a lot of reading on here.
I am trying the same on the XSLT file (I figured it's just more XML) but I can't figure out the XPath syntax I need to get the values I want out.
I have the following SQL and XSLT structure and data
DECLARE @tbl as TABLE (ID INT IDENTITY(1,1), xmldata XML)
DECLARE @xml as XML
set @xml = '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
exclude-result-prefixes="xs xsi xsl"
xmlns:q1="q1:lib">
<xsl:output method="xml" encoding="UTF-8" indent="yes"/>
<xsl:template match="/">
<SUpdateCollection xmlns="http://www.q1.com">
<xsl:for-each select="//AIncs/AInc">
<SUpdate>
<Header>
<AType>ThisType</AType>
<SGroup>Group1</SGroup>
</Header>
<RValues>
<RValue>
<UID>
<xsl:value-of select="UID"/>
</UID>
<QID>100000</QID>
<RID>2</RID>
<SToken>
<xsl:value-of select="ID"/>
</SToken>
<RData>
<xsl:value-of select="DATATYPE"/>
</RData>
</RValue>
<xsl:if test ="DATE != ''">
<RValue>
<UID>
<xsl:value-of select="UID"/>
</UID>
<QID>100031</QID>
<RID>27</RID>
<SToken>
<xsl:value-of select="ID"/>
</SToken>
<RData>
<xsl:value-of select="q1:ValidSQLDate(DATE)"/>
</RData>
</RValue>
</xsl:if>
<xsl:if test ="XTYPE != ''">
<RValue>
<UID>
<xsl:value-of select="UID"/>
</UID>
<QID>100013</QID>
<RID>2</RID>
<SToken>
<xsl:value-of select="ID"/>
</SToken>
<RData>
<xsl:value-of select="XTYPE"/>
</RData>
</RValue>
</xsl:if>
</RValues>
</SUpdate>
</xsl:for-each>
</SUpdateCollection>
</xsl:template>
</xsl:stylesheet>'
insert into @tbl(xmldata)
select @xml
I have managed to get a basic query to work that displays all of the node names and some of the values I think.
select t.ID
,NodeName = n.value('local-name(.)','nvarchar(max)')
,NodeValue = n.value('text()[1]','nvarchar(max)')
,RData = n.value('(../../../../../../@select)[1]','nvarchar(max)')
from @tbl t
cross apply t.xmldata.nodes('//*') a(n)
Which gives a column of all the xml tags in NodeName and some values but mostly NULLS for NodeValue and //AIncs/AInc for the RData value-of row. I must have tried about 50 combinations of XPath syntax in the cross apply but this is the only one I got to give me anything close to the data I need.
I think I need help getting the XPath syntax correct in the cross apply but it's defeating me maybe because I am trying to load an XSLT as XML?
My goal is to get the AType, SGroup, QID and RID values which the above will give me but crucially I also need the corresponding RData select in the xsl:value-of tag.
the ultimate output should be something like:
|RData |QID |RID|
|DATATYPE |100000 |2 |
|DATE |100031 |27 |
|XTYPE |100013 |2 |
But I know I can get that if I can get the RData column in the select to work.
I have also looked at trying to apply the transform to the XML as I know I could import the resulting XML but I need to do this in SQL and the only ways I have seen of doing that via a Google search involve compiling C# code and where this needs to be done that would be a nonstarter even if I could get it to work which I couldn't.
So any help greatly appreciated.
You need to use WITH XMLNAMESPACES
to be able to access nodes which are in a non-empty namespace. Easiest to declare the DEFAULT
namespace as the one with the most nodes. Don't use /*/local-name(.)
it's really inefficient.
Also:
//
descendant-axis unless absolutely necessary, as it's also inefficient./text()
to get the text value of a node, rather than relying on implicit conversions.WITH XMLNAMESPACES(
DEFAULT N'http://www.q1.com',
N'http://www.w3.org/1999/XSL/Transform' AS xsl
)
SELECT
RData = x1.rvalue.value('(RData/xsl:value-of/@select)[1]', 'nvarchar(1000)'),
QID = x1.rvalue.value('(RID/text())[1]', 'nvarchar(1000)'),
RID = x1.rvalue.value('(QID/text())[1]', 'nvarchar(1000)')
FROM @tbl t
CROSS APPLY t.xmldata.nodes('
xsl:stylesheet/xsl:template/SUpdateCollection/xsl:for-each/SUpdate/RValues//RValue
') x1(rvalue);