Import and parse XSLT file using XML

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.

Solution

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:

Don't use // descendant-axis unless absolutely necessary, as it's also inefficient.
Use /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);

db<>fiddle