patternsqlMinor

T-SQL dynamically import XML into tables (SQL Server 2014)

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablesdynamicallysqlintoxmlserver2014import

Problem

I have a XML response with this structure but with some 150 different nodes:


    
        334
        17.10
        marc58
        
            Marc Juppé
            Rue
            Paris
            
            FR
            333333333
            22222
         
         4.50
         
            
               
                    3664
                    47
                    MCPU DDA010
                    mmx
                
                1
                6.2
            
            
               
                    3665
                    45
                    MCPU DFZ42
                    mmy
                
                2
                3.2

I need to store this info into 3 different tables and for Item table, I need to create a record for each different `

, but inserting also the Order Node details; like this:

|ItemID|Store|Title |SKU|Quantity|Price|OrderID|AmountPaid|UserID|ShippingCost|
|3664  |   47|DDA010|mmx|       1|  6.2|    334|     17.10|marc58|        4.50|
|3665  |   45|DFZ42 |mmy|       2|  3.2|    334|     17.10|marc58|        4.50|


To write in the different tables the required info "automatically", I built with great help of the community this query:

``
Set @T1='Orders'
Set @F1='OrderID'
Set @V=''

SELECT
@C= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@C)=0, CONCAT( ISNULL(@C + ',','') , QUOTENAME(T.X.value('local-name(.)', 'nvarchar(100)'))), @C),
@D= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@CP)=0, CONCAT( ISNULL(@D + ',N','') , '''', T.X.value(N'text()[1]', 'nvarchar(max)'),''''), @D),
@U= IIF (CHARINDEX('['+T.X.value('local-name(.)', 'nvarchar(100)')+']',@CP)=0, CONCAT( ISNULL(@U + ',','') , QUOTENAME( T.X.value('local-name(.)', 'nvarchar(100)')) ,'=', '''',T.X.value(N'text()[1]', 'nvarchar(max)'),''''), @U),
@V= IIF(T.X.value('local-name(.)', 'nvarchar(100)') =@F2, T.X.value('tex

Solution

Bad news: the local-name(.) plus @XML.nodes('//[count(child::) = 0]') approach in your script completely flattens out the XML document, and will need to be reworked if you have any multiple-X-per-Y structures in your document. If the dynamic SQL is a requirement, then please provide an example to test that aspect more easily.

Another approach might be to build queries you need manually. If your fundamental problem is including parent info, then you might modify my demo SELECT query below.

(N.B. Mikael Eriksson's answer has an improved query. Please refer to that.)

Key ideas:

. is the context node, and .. gives you the parent

.nodes is a different kind of XML function that belongs in the FROM section of the query and is usually seen with CROSS APPLY. It returns a pointer per match, and is what allows working with multiple rows. Read more here.

.query().value is one of several methods for letting SQL Server know the value method only has one piece of data to work with (fixing the "requires a singleton" error)

DECLARE @XML xml = 
'
    
        334
        17.10
        marc58
        
            Marc Juppé
            Rue
            Paris
            
            FR
            333333333
            22222
         
         4.50
         
            
               
                    3664
                    47
                    MCPU DDA010
                    mmx
                
                1
                6.2
            
            
               
                    3665
                    45
                    MCPU DFZ42
                    mmy
                
                2
                3.2
            
        
    
'

SELECT 
    x.value('./ItemID[1]','int') AS ItemID,
    x.value('./Store[1]','int') AS Store,
    x.value('./Title[1]','nvarchar(100)') AS Title,
    x.value('./SKU[1]','nvarchar(100)') AS SKU,
    x.value('../Quantity[1]','int') AS Qty,
    x.value('../Price[1]','decimal(11,2)') AS Price,
    x.query('//OrderID[1]').value('.','int') AS OrderID,
    x.query('//AmountPaid[1]').value('.','decimal(11,2)') AS AmountPaid,
    x.query('//UserID[1]').value('.','nvarchar(100)') AS UserID,
    x.query('//ShippingCosts[1]').value('.','decimal(11,2)') AS ShippingCosts
FROM @XML.nodes('//Item/Details') i(x)

Code Snippets

DECLARE @XML xml = 
'<Orders>
    <Order>
        <OrderID>334</OrderID>
        <AmountPaid currencyID="EUR">17.10</AmountPaid>
        <UserID>marc58</UserID>
        <ShippingAddress>
            <Name>Marc Juppé</Name>
            <Address>Rue</Address>
            <City>Paris</City>
            <StateOrProvince></StateOrProvince>
            <Country>FR</Country>
            <Phone>333333333</Phone>
            <PostalCode>22222</PostalCode>
         </ShippingAddress>
         <ShippingCosts>4.50</ShippingCosts>
         <Items>
            <Item>
               <Details>
                    <ItemID>3664</ItemID>
                    <Store>47</Store>
                    <Title>MCPU DDA010</Title>
                    <SKU>mmx</SKU>
                </Details>
                <Quantity>1</Quantity>
                <Price currencyID="EUR">6.2</Price>
            </Item>
            <Item>
               <Details>
                    <ItemID>3665</ItemID>
                    <Store>45</Store>
                    <Title>MCPU DFZ42</Title>
                    <SKU>mmy</SKU>
                </Details>
                <Quantity>2</Quantity>
                <Price currencyID="EUR">3.2</Price>
            </Item>
        </Items>
    </Order>
</Orders>'

SELECT 
    x.value('./ItemID[1]','int') AS ItemID,
    x.value('./Store[1]','int') AS Store,
    x.value('./Title[1]','nvarchar(100)') AS Title,
    x.value('./SKU[1]','nvarchar(100)') AS SKU,
    x.value('../Quantity[1]','int') AS Qty,
    x.value('../Price[1]','decimal(11,2)') AS Price,
    x.query('//OrderID[1]').value('.','int') AS OrderID,
    x.query('//AmountPaid[1]').value('.','decimal(11,2)') AS AmountPaid,
    x.query('//UserID[1]').value('.','nvarchar(100)') AS UserID,
    x.query('//ShippingCosts[1]').value('.','decimal(11,2)') AS ShippingCosts
FROM @XML.nodes('//Item/Details') i(x)

Context

StackExchange Database Administrators Q#185376, answer score: 7

Revisions (0)

No revisions yet.