Import and parse XSLT file using XML

Question

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.

What's the purpose of shredding the XSLT file down to it's base components, why not just store it as it is. It's useless to use for transformation if you break it down — siggemannen
– siggemannen, Commented Jul 9 at 9:40
Because the legacy XML config file has the RData values in it's nodes but doesn't have the QID and RID values. If I can get all of the data from the XSLT (there are many more than the 3 I used as examples) I can match them as I need the QID and RID with some parameters from the XML file. If they had put those two values in the XML file I wouldn't need to do this but hey we have to deal with what we have and make the best of it. I have tried to apply the transform via a C# SQL ASSEMBLY but they all seem to convert to nvarchars to do whatever they are doing and there are more than 4000 chars — iFreeman72
– iFreeman72, Commented Jul 9 at 10:14
It's XML, so namespaces matter. Notice how when you get to the SUpdateCollection node the default namespace changes to http://www.q1.com, try including NS = n.value('namespace-uri(.)', 'nvarchar(max)') in your query to see how that affects things. — AlwaysLearning
– AlwaysLearning, Commented Jul 9 at 11:02
Thank you AlwaysLearning. That is enlightening and does help as does Charlieface's solution below. — iFreeman72
– iFreeman72, Commented Jul 9 at 11:16
You should have been able to use a C# SQLCLR assembly, not sure what you mean by "they all seem to convert to nvarchars to do whatever they are doing and there are more than 4000 chars" you can pass a SqlXml type quite easily. — Charlieface
– Charlieface, Commented Jul 9 at 11:17

Charlieface · Accepted Answer · 2025-07-09 10:55:32Z

4

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

answered Jul 9 at 10:55

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iFreeman72 Jul 9 at 11:18

Thank you Charlieface. This just goes to prove what I suspected I just don't know enough about XML and especially transforms. Your solution is perfect. Unfortunately I can't up vote it as I am too new apparently. Hopefully someone will up vote it soon for you.

Collectives™ on Stack Overflow

Import and parse XSLT file using XML

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related