I am trying to insert XML into SQL table but it is either adding first row or only one column.

Here is my XML

<SessionDataIds>
    <SelectedIds>
       <SelectedId>388439424</SelectedId>
       <SelectedId>388436066</SelectedId>
       <SelectedId>388435999</SelectedId>
       <SelectedId>388439450</SelectedId>
       <SelectedId>388439463</SelectedId>
       <SelectedId>388433795</SelectedId>
    </SelectedIds>
    <VendorId>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609003</VendorIds>
    </VendorId>
</SessionDataIds>

Please help

Below are the solutions I tried

solution #1

INSERT INTO #sessionData_CTE (sessionDataId,vendorId)
SELECT DISTINCT
    T.C.value('(SelectedIds/SelectedId/text())[1]', 'int')AS sessionDataId,
    T.C.value('(VendorId/VendorIds/text())[2]', 'int')AS vendorId
from @sessionDataIdXmlInner.nodes('//SessionDataIds') as T(C);

CodePudding user response：

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

The sessionDataId and the vendorId are tied based on a sequential order in the XML.

SQL

DECLARE @xml XML =
N'<SessionDataIds>
    <SelectedIds>
       <SelectedId>388439424</SelectedId>
       <SelectedId>388436066</SelectedId>
       <SelectedId>388435999</SelectedId>
       <SelectedId>388439450</SelectedId>
       <SelectedId>388439463</SelectedId>
       <SelectedId>388433795</SelectedId>
    </SelectedIds>
    <VendorId>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609004</VendorIds>
        <VendorIds>609003</VendorIds>
    </VendorId>
</SessionDataIds>';

;WITH rs1 AS
(
    SELECT id = ROW_NUMBER() OVER (ORDER BY @@SPID)
        , c.value('(text())[1]', 'int')AS sessionDataId
    from @xml.nodes('/SessionDataIds/SelectedIds/SelectedId') AS t(c)
), rs2 AS
(
    SELECT id = ROW_NUMBER() OVER (ORDER BY @@SPID)
        , c.value('(text())[1]', 'int')AS vendorId
    from @xml.nodes('/SessionDataIds/VendorId/VendorIds') AS t(c)
)
SELECT rs1.* , rs2.vendorId
FROM rs1 INNER JOIN rs2 ON rs2.id = rs1.id;

Output