XQuery SQL XML values

Question

We have to find a query parsing XML to have a result like this one:

COL_ACTION N           COL_NAME_I       COL_VALUE_AFTER
---------- ----------- ---------------- -------------------
INS        1           N                1
INS        1           TST_ID           28
INS        1           TST_DATA         data2
INS        2           N                2
INS        2           TST_ID           27
INS        2           TST_DATA         data1

The value of column N depend on the value of the first "row" (COL_NAME_I = N) of the XML dataset

The XML contains:

DECLARE @XML XML =
N'
<DATASET>
  <XML_INS>
    <IROW xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <N>1</N>
      <TST_ID>28</TST_ID>
      <TST_DATA>data2</TST_DATA>
    </IROW>
    <IROW xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <N>2</N>
      <TST_ID>27</TST_ID>
      <TST_DATA>data1</TST_DATA>
    </IROW>
  </XML_INS>
</DATASET>
';

The query I was able to do is:

SELECT RIGHT(ca.value('local-name(.)','CHAR(7)'), 3) AS COL_ACTION,
       x.value('(//N)[1]', 'int') AS N,

--       cn.value('./text()[1]','int') AS NI,

       ci.value('local-name(.)','sysname') AS COL_NAME_I,
       ci.value('./text()[1]','nvarchar(max)') AS COL_VALUE_AFTER 
FROM   @XML.nodes('.') AS T(x)
       OUTER APPLY @XML.nodes('DATASET/*') AS TA(ca)
       OUTER APPLY @XML.nodes('DATASET/XML_INS/IROW/N/*') AS TN(cn)
       OUTER APPLY @XML.nodes('DATASET/XML_INS/IROW/*') AS TI(ci);

The problem remains on the "N" column that does no have the expected value:

COL_ACTION N           COL_NAME_I       COL_VALUE_AFTER
---------- ----------- ---------------- -------------------
INS        1           N                1
INS        1           TST_ID           28
INS        1           TST_DATA         data2
INS        1           N                2
INS        1           TST_ID           27
INS        1           TST_DATA         data1

I tryed different approach, as seen in SQL comment, but does not produce the good result...

Answer 1

You need each .nodes to refer to the previous, in order to only bring back its children. Otherwise you get every descendant node for the whole document.

SELECT RIGHT(action.value('local-name(.)','CHAR(7)'), 3) AS COL_ACTION,
       irow.value('(N/text())[1]', 'int') AS N,

       ci.value('local-name(.)','sysname') AS COL_NAME_I,
       ci.value('text()[1]','nvarchar(max)') AS COL_VALUE_AFTER 
FROM   @XML.nodes('DATASET/*') AS x1(action)
OUTER APPLY x1.action.nodes('IROW') AS x2(irow)
OUTER APPLY x2.irow.nodes('*') AS TI(ci);

db<>fiddle

Answer 2

This should do it:

SELECT
    irow.value('local-name(..)', 'NVARCHAR(100)') AS COL_ACTION,
    irow.value('(./N)[1]', 'NVARCHAR(100)') AS N,
    child.value('local-name(.)', 'NVARCHAR(100)') AS COL_NAME_I,
    child.value('.', 'NVARCHAR(100)') AS COL_VALUE_AFTER
FROM @XML.nodes('/DATASET/*/IROW') AS n1(irow)
CROSS APPLY irow.nodes('./*') AS n2(child)

Answer 3

i am a new contributer so sorry if i got something wrong.

I used this approach on my work : Link

My sample working code :

    CREATE TABLE #FieldPermissionsTable
    ( 
        TE_ID VARCHAR(MAX),
        Value VARCHAR(MAX),
        Value2 VARCHAR(MAX),
        NAVI_USER VARCHAR(MAX)
    ) 

--SET @FieldPermissionAccessXML = '<Row_ID><Elements ID="1" Value="1" Value2="NULLnROLLA" Navi_User="testuser" /><Elements ID="3" Value="tespit" Value2="NULLnROLLA" Navi_User="testuser" /><Elements ID="6" Value="aciklama" Value2="NULLnROLLA" Navi_User="testuser" /></Row_ID>'

-- SELECT @FieldPermissionAccessXML = [dbo].[to_xml_replace] (@FieldPermissionAccessXML)

    EXEC sp_xml_preparedocument @XmlHandle output,@FieldPermissionAccessXML
        --'<FieldPermissions>
        --<FieldPermission FieldName="" RoleID="1" Access="1" />
        --<FieldPermission FieldName="" RoleID="2" Access="1" />'
        --select * from JMP_FieldPermissions
    INSERT INTO #FieldPermissionsTable 
    SELECT *--ID,Value, Value2, Navi_User 
    FROM  OPENXML (@XmlHandle, './Row_ID/Elements') 
          WITH (TE_ID VARCHAR(MAX) '@ID',
        Value VARCHAR(MAX) '@Value',
        Value2 VARCHAR(MAX) '@Value2',
        NAVI_USER VARCHAR(MAX) '@Navi_User'
    
                )