'XML Cross Apply Assist - Secondary level and multiple rows

I was wondering if someone could assist me with some XML work I am doing on SQL, frustratingly theyve asked for further information and struggling.

Attached is the XML:

<pagedata>
  <BinderSections REPEATINGTYPE="PageList">
    <rowdata REPEATINGINDEX="1">
       <Compliance>
        <EstPolicyCount REPEATINGTYPE="PropertyGroup">
          <rowdata REPEATINGINDEX="Total12">30</rowdata>
          <rowdata REPEATINGINDEX="NonSME">30</rowdata>
        </EstPolicyCount>
      </Compliance>
    </rowdata>
  </BinderSections>
</pagedata>

I need to use in an XML query, I can get most of it using the following SQL:


DECLARE @id INT 
DECLARE @SQL NVARCHAR(MAX)

SET @id = 1

WHILE (@id <= 1)
BEGIN

SET @SQL = '
SELECT
  t.TransID
  --BinderSection.value(N''(rowdata[@REPEATINGINDEX="'+CAST(@id as varchar(MAX))+'"]/Compliance/EstPolicyCount[@REPEATINGTYPE="PropertyGroup"]''/rowdata[@REPEATINGINDEX="Total12"/text())[1]'', N''nvarchar(MAX)'') as [Total12]
FROM  (SELECT TransID, CAST(TransXML as XML) as TransXML FROM  [dbo].[DA_TRANSACTIONS]) t
CROSS APPLY TransXML.nodes(''pagedata/BinderSections[@REPEATINGTYPE="PageList"]'') as pagedata(BinderSection)
--CROSS APPLY BinderSection.nodes(''Compliance/EstPolicyCount[@REPEATINGTYPE="PropertyGroup"]'') as pagedata2(EstPolicyCount)
WHERE  t.TransID = ''B-2511''';

EXEC (@SQL)

SET @id = @id + 1
END

(I am not sure why its not turning into code for stackoverflow - i am new)

The part I am trying to get is this:

<EstPolicyCount REPEATINGTYPE="PropertyGroup">
<rowdata REPEATINGINDEX="Total12">30</rowdata>
<rowdata REPEATINGINDEX="NonSME">30</rowdata>

The output I would want is

TransID Total12 TotalSME
B-2511 30 30

SQL version is Microsoft SQL Server 2016 (SP2)

Thanks

Nick

sqlxmlapply


Solution 1:[1]

A minimal reproducible example is not provided. So, I am shooting from the hip.

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @DA_TRANSACTIONS TABLE (TransID VARCHAR(10) PRIMARY KEY, TransXML NVARCHAR(MAX));
INSERT INTO @DA_TRANSACTIONS (TransID, TransXML) VALUES
('B-2511', N'<pagedata>
  <BinderSections REPEATINGTYPE="PageList">
    <rowdata REPEATINGINDEX="1">
       <Compliance>
        <EstPolicyCount REPEATINGTYPE="PropertyGroup">
          <rowdata REPEATINGINDEX="Total12">30</rowdata>
          <rowdata REPEATINGINDEX="NonSME">30</rowdata>
        </EstPolicyCount>
      </Compliance>
    </rowdata>
  </BinderSections>
</pagedata>');
-- DDL and sample data population, end

SELECT t.TransID
    , Total12 = c.value('(rowdata[@REPEATINGINDEX="Total12"]/text())[1]', 'INT')
    , TotalSME = c.value('(rowdata[@REPEATINGINDEX="NonSME"]/text())[1]', 'INT')
FROM @DA_TRANSACTIONS AS t
    CROSS APPLY (SELECT TrXML = TRY_CAST(TransXML AS XML)) x
    CROSS APPLY TrXML.nodes('/pagedata/BinderSections/rowdata/Compliance/EstPolicyCount') AS t1(c);

Output

+---------+---------+----------+
| TransID | Total12 | TotalSME |
+---------+---------+----------+
| B-2511  |      30 |       30 |
+---------+---------+----------+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions