Hi all!

I have an XML like this:

<?xml version="1.0" ?>
<actions>
  <action code="ABC1234" number="1">
    <group value="2">
      <subgroup value="5">
        <d value="02">
          <myType code="M">
            <row m="ABCDF123" q="3" n="3.75" l="4.5"/>
            <row m="GHJKS123" q="10" n="12" l="4"/>
          </myType>
          <myType code="L">
            <row m="ABCDF123" q="3" n="3.75" l="4.5"/>
            <row m="GHJKS123" q="10" n="12" l="4"/>
          </myType>
        </giorno>
      </subgroup>
      <subgroup value="6">
        <d value="2">
...
        <d value="3">
...


Now I have this XML into a XML column in a table A.
I have a table (denormalized) B like this:

codeAction varchar(20),
number int,
groupValue int,
subgroupValue int,
dvalue int,
myTypeCode char(1),
m varchar(20),
q int,
n decimal(10,2),
l decimal(10,2)

The question is: it's possible to transform the XML data into the relational
table?
Maybe with nodes()... but I don't know...

Many thanks!
A.

DECLARE @x XML

SET @x = '<?xml version="1.0" ?>
<actions>
    <action code="ABC1234" number="1">
        <group value="2">
            <subgroup value="5">
                <d value="02">
                    <myType code="M">
                        <row m="ABCDF123" q="3" n="3.75" l="4.5"/>
                        <row m="GHJKS123" q="10" n="12" l="4"/>
                    </myType>
                    <myType code="L">
                        <row m="ABCDF123" q="3" n="3.75" l="4.5"/>
                        <row m="GHJKS123" q="10" n="12" l="4"/>
                    </myType>
                </d>
            </subgroup>
            <subgroup value="6">
                <d value="04">
                    <myType code="N">
                        <row m="ABCDF123" q="3" n="3.75" l="4.5"/>
                        <row m="GHJKS123" q="10" n="12" l="4"/>
                    </myType>
                    <myType code="P">
                        <row m="ABCDF123" q="3" n="3.75" l="4.5"/>
                        <row m="GHJKS123" q="10" n="12" l="4"/>
                    </myType>
                </d>
            </subgroup>
        </group>
    </action>
</actions>
'
DECLARE @t TABLE
(
    codeAction varchar(20),
    number int,
    groupValue int,
    subgroupValue int,
    dvalue int,
    myTypeCode char(1),
    m varchar(20),
    q int,
    n decimal(10,2),
    l decimal(10,2)
)

INSERT INTO @t
SELECT
    t.c.value('../../../../../@code', 'VARCHAR(20)') AS codeAction,
    t.c.value('../../../../../@number', 'INT') AS number,
    t.c.value('../../../../@value', 'INT') AS groupValue,
    t.c.value('../../../@value', 'INT') AS subgroupValue,
    t.c.value('../../@value', 'INT') AS dvalue,
    t.c.value('../@code', 'CHAR(1)') AS myTypeCode,
    t.c.value('@m', 'VARCHAR(20)') AS m,
    t.c.value('@q', 'INT') AS q,
    t.c.value('@n', 'DECIMAL(10,2)') AS n,
    t.c.value('@l', 'DECIMAL(10,2)') AS l
FROM @x.nodes('actions/action/group/subgroup/d/myType/row') AS t(c)

SELECT * FROM @t

Show quoteHide quote

Many thanks Peter!
A.

How to output table as XML with elements on separate lines and indented?
modify() Top-level attribute nodes are not supported Error
Can WordML be put in a SQL Server XML column?
How to define the XML schema for a SQLXMLBulkLoad
Get data out of a table using a schema through select statement