SQL拆分XML插入关联表失败，第二表插入返回NULL求解决

Hey there, let's troubleshoot why your second INSERT is returning NULL and failing to link TableII records to their corresponding TableI entries. Based on common pitfalls with XML-to-relational inserts and parent-child table mapping, here are the key areas to check step by step:

1. Verify you're correctly capturing the parent TableI identity/key value #

If TableI uses an auto-incrementing IDENTITY column, the most common mistake here is using the wrong function to grab the newly generated ID. Stick with SCOPE_IDENTITY() instead of @@IDENTITY—the latter pulls the last identity value across the entire SQL Server instance, which can be corrupted by triggers or other concurrent operations.

Example of correct ID capture for a single XML row:

DECLARE @TableIId INT;

-- Insert PartI data into TableI
INSERT INTO TableI (PartICol1, PartICol2)
SELECT 
  XmlRow.value('(/Root/PartI/Col1)[1]', 'VARCHAR(100)'),
  XmlRow.value('(/Root/PartI/Col2)[1]', 'INT')
FROM XMLDATA;

-- Grab the ID of the row we just inserted
SET @TableIId = SCOPE_IDENTITY();

If you're processing multiple rows in XMLDATA (each with its own PartI + PartII), a single variable won't work—use the OUTPUT clause to map inserted TableI IDs back to their source XML rows:

-- Temp table to hold source XML row IDs and their corresponding TableI IDs
DECLARE @InsertedParentIds TABLE (XMLDATAId INT, TableIId INT);

INSERT INTO TableI (PartICol1, PartICol2)
OUTPUT XMLDATA.Id, inserted.TableIId INTO @InsertedParentIds
SELECT 
  XmlRow.value('(/Root/PartI/Col1)[1]', 'VARCHAR(100)'),
  XmlRow.value('(/Root/PartI/Col2)[1]', 'INT')
FROM XMLDATA;

2. Fix XML parsing paths for PartII SEQUENCE nodes #

If your INSERT into TableII is returning NULL, chances are your XPath is targeting the wrong nodes, resulting in no data being pulled from the XML. First, test your XML parsing in isolation to confirm you're getting PartII data:

-- Test if you can extract SEQUENCE data correctly
SELECT 
  seq.value('(SequenceCol1)[1]', 'VARCHAR(100)'),
  seq.value('(SequenceCol2)[1]', 'INT')
FROM XMLDATA
CROSS APPLY XmlRow.nodes('/Root/PartII/SEQUENCE') AS s(seq);

If this query returns empty results, double-check your XML structure:

• Is the path correct? For example, if your XML uses nested nodes like <Root><PartII><Sequences><SEQUENCE>...</SEQUENCE></Sequences></PartII></Root>, your XPath should be /Root/PartII/Sequences/SEQUENCE.
• Does the XML use namespaces? If so, you need to declare them with WITH XMLNAMESPACES to parse nodes correctly:

  WITH XMLNAMESPACES (DEFAULT 'http://your-namespace-url.com')
  SELECT ... FROM XMLDATA CROSS APPLY XmlRow.nodes('/Root/PartII/SEQUENCE') AS s(seq);
  

3. Ensure the parent-child link is properly mapped in TableII's INSERT #

Once you have the correct TableI ID and valid PartII data, make sure you're explicitly passing the parent ID into TableII's foreign key column. For multiple rows, use the temp table we created earlier to join back to the source XML:

INSERT INTO TableII (TableIId, SequenceCol1, SequenceCol2)
SELECT 
  ipi.TableIId, -- This is the critical parent link—no more NULLs!
  seq.value('(SequenceCol1)[1]', 'VARCHAR(100)'),
  seq.value('(SequenceCol2)[1]', 'INT')
FROM XMLDATA x
JOIN @InsertedParentIds ipi ON x.Id = ipi.XMLDATAId
CROSS APPLY x.XmlRow.nodes('/Root/PartII/SEQUENCE') AS s(seq);

4. Check for NULL constraints or missing XML nodes #

If your TableII foreign key column is set to NOT NULL, any failure to pass the parent ID will throw an error—but if it allows NULLs, you'll get NULL values instead. Confirm:

• The foreign key column in TableII is correctly linked to TableI's primary key.
• The XML nodes you're parsing for PartII actually exist (no typos in node names, case sensitivity matters in XPath!).

If you've gone through all these steps and still see NULLs, share your exact INSERT scripts and a sample of your XML data—we can dig deeper into the specific issue.

内容的提问来源于stack exchange，提问作者Mehrad Eslami