如何在SQL中根据XML节点属性将同名节点插入不同数据表

Hey there! Let's work through how to split your XML <dob> nodes into Table A and Table B using SQL. I'll use SQL Server's XML handling functions as an example (the logic can be adapted to other databases like PostgreSQL or Oracle with their own XML tools too).

First, I fixed a tiny typo in your original XML (the third <dob> had 02/1]7/1920 which I changed to 02/17/1920—I assume that was a typo!).

Step 1: Load the XML Data #

First, we'll store the XML in a variable (or you can use a table column if your XML is already stored in a database table):

DECLARE @xmlData XML = N'<xml>
  <entities>
    <entity id="147297" version="20150209161956">
      <dobs>
        <dob Y="1970">10/16/1970</dob>
        <dob D="17" M="10" Y="1970">10/17/1970</dob>
        <dob D="02" M="7" Y="1920">02/17/1920</dob>
        <dob D="1" M="9" Y="1990">10/9/1990</dob>
        <dob Y="1870">10/16/1870</dob>
      </dobs>
    </entity>
  </entities>
</xml>';

Step 2: Define Your Target Tables #

Let's assume your tables have these structures (adjust column names/types to match your actual needs):

-- Table A: For <dob> nodes with D, M, Y attributes
CREATE TABLE IF NOT EXISTS TableA (
    Day INT,
    Month INT,
    Year INT,
    FullDateString VARCHAR(20)
);

-- Table B: For <dob> nodes with ONLY Y attribute
CREATE TABLE IF NOT EXISTS TableB (
    Year INT,
    FullDateString VARCHAR(20)
);

Step 3: Insert into Table A #

We'll use the .nodes() method to iterate over each <dob> node, then filter for nodes that have all three attributes (D, M, Y):

INSERT INTO TableA (Day, Month, Year, FullDateString)
SELECT
    dobNode.value('@D', 'INT') AS Day,
    dobNode.value('@M', 'INT') AS Month,
    dobNode.value('@Y', 'INT') AS Year,
    dobNode.value('.', 'VARCHAR(20)') AS FullDateString
FROM @xmlData.nodes('/xml/entities/entity/dobs/dob') AS T(dobNode)
WHERE
    dobNode.exist('@D') = 1  -- Check if D attribute exists
    AND dobNode.exist('@M') = 1  -- Check if M attribute exists
    AND dobNode.exist('@Y') = 1;  -- Check if Y attribute exists

Step 4: Insert into Table B #

Now we'll filter for <dob> nodes that only have the Y attribute (no D or M attributes):

INSERT INTO TableB (Year, FullDateString)
SELECT
    dobNode.value('@Y', 'INT') AS Year,
    dobNode.value('.', 'VARCHAR(20)') AS FullDateString
FROM @xmlData.nodes('/xml/entities/entity/dobs/dob') AS T(dobNode)
WHERE
    dobNode.exist('@D') = 0  -- No D attribute
    AND dobNode.exist('@M') = 0  -- No M attribute
    AND dobNode.exist('@Y') = 1;  -- Y attribute exists

If Your XML Is Stored in a Table Column #

If your XML isn't in a variable but in a table column (e.g., a table named XmlStorage with column XmlContent), use CROSS APPLY to link the nodes to the table rows:

-- Insert into TableA from a table column
INSERT INTO TableA (Day, Month, Year, FullDateString)
SELECT
    dobNode.value('@D', 'INT') AS Day,
    dobNode.value('@M', 'INT') AS Month,
    dobNode.value('@Y', 'INT') AS Year,
    dobNode.value('.', 'VARCHAR(20)') AS FullDateString
FROM XmlStorage
CROSS APPLY XmlStorage.XmlContent.nodes('/xml/entities/entity/dobs/dob') AS T(dobNode)
WHERE
    dobNode.exist('@D') = 1
    AND dobNode.exist('@M') = 1
    AND dobNode.exist('@Y') = 1;

This approach ensures you only insert the correct nodes into each table based on their attributes.

内容的提问来源于stack exchange，提问作者Prawin Arumugam