Read xml with dynamic rows and attributes in sql server 2012

Question

I have below typed xml

 <root>
    <row1 invoice_number="WEDRT" vendor="Telekm" amount="233" status="1" created_date="42590" />
    <row2 invoice_number="MSFDRT" vendor="ARS" amount="344" status="1" created_date="42955" />
</root>

This xml is generated dynamically. By dynamic I mean at the time of writing the sql code to parse it I don't know how many rows and attributes would be there. There could be as many rows as 100,000 and as many attributes as user wants. I won't even know the name of the attributes.

Is it possible to put it in a temp table by producing following type of result out of this xml?

Invoice_number     Vendor     amount   status    created_Date
WEDRT              TELEKOM     233       1        42590
MSFDRT             ARS         344       1        42955

I have tried the following

    DECLARE
    @xml    XML,
    @Columns VARCHAR(MAX)

SET @xml = '
<root>
<row1 invoice_number="WEDRT" vendor="Telekm" amount="233" status="1" created_date="42590" />
<row2 invoice_number="MSFDRT" vendor="ARS" amount="344" status="1" created_date="42955" />
</root>
'
BEGIN   
    SET NOCOUNT ON;

        DECLARE @COUNT INT, @COUNTER INT, @TQUERY   VARCHAR(2000), @SELECT  VARCHAR(MAX), @VALUES   VARCHAR(MAX)
        SELECT * INTO #TEMPTABLE
        FROM(
        SELECT  
            CAST(x.v.query('local-name(.)') AS VARCHAR(100)) As AttributeName,
            v.value('.' , 'VARCHAR(100)') AS Value
            FROM @XML.nodes('//@*') x(v)
            ) A

        SELECT * FROM #TEMPTABLE 

        DROP TABLE #TEMPTABLE
END

It gives me result like below table for 2 rows in xml

AttributeName     Value
invoice_number    WEDRT
vendor            Telekom
amount            233
status            1
created_Date      42590
invoice_number    MSFDRT
vendor            ARS
amount            344
status            1
created_Date      42955

What do you mean by "read"? What do you need from it in the end, in which form? — Roger Wolf
– Roger Wolf, Commented Sep 24, 2016 at 3:38
You have a nice working solution, you should add row_number to your output, or some identity to ech invoice and then just PIVOT — gofr1
– gofr1, Commented Sep 24, 2016 at 6:18
@gofr1: Pivoting is fine but as you can see the attribute names are getting repeated after 5 rows. That's why i guess i can't pivot. — Rishi Bharadwaj
– Rishi Bharadwaj, Commented Sep 24, 2016 at 6:40

gofr1 · Accepted Answer · 2016-09-24 07:10:53Z

4

I have modified your query, hope this helps.

DECLARE @xml    XML,
        @Columns VARCHAR(MAX)

SET @xml = '
<root>
<row1 invoice_number="WEDRT" vendor="Telekm" amount="233" status="1" created_date="42590" />
<row2 invoice_number="MSFDRT" vendor="ARS" amount="344" status="1" created_date="42955" />
</root>
'
BEGIN   
    SET NOCOUNT ON;

        DECLARE @COUNT INT, @COUNTER INT, @TQUERY   VARCHAR(2000), @SELECT  VARCHAR(MAX), @VALUES   VARCHAR(MAX)

        SELECT * INTO #TEMPTABLE
        FROM(
            SELECT  CAST(x.v.query('local-name(.)') AS VARCHAR(100)) As AttributeName,
                    x.v.value('.' , 'VARCHAR(100)') AS [Value],
                    CAST(x.v.query('local-name(..)') AS VARCHAR(100)) As RowNumber
            FROM @XML.nodes('//*//@*') x(v)
        ) A

        DECLARE @t TABLE (AttributeName nvarchar(max), r int)
        DECLARE @sql nvarchar(max),
                @col nvarchar(max)

        INSERT INTO @t
        SELECT DISTINCT AttributeName,
                ROW_NUMBER() OVER(PARTITION BY RowNumber ORDER BY RowNumber) as r
        FROM #TEMPTABLE 

        SELECT @col = (
            SELECT ','+QUOTENAME(AttributeName)
            FROM @t
            ORDER BY r
            FOR XML PATH('')
        )


        SELECT @sql = N'
        SELECT *
        FROM #TEMPTABLE t
        PIVOT (
            MAX([Value]) FOR AttributeName IN ('+STUFF(@col,1,1,'')+')
        ) as pvt'

        EXEC sp_executesql @sql

        DROP TABLE #TEMPTABLE
END

Output:

RowNumber   invoice_number  vendor  amount  status  created_date
row1        WEDRT           Telekm  233     1       42590
row2        MSFDRT          ARS     344     1       42955

The main idea is to dynamically pivot your results. To get rid of RowNumber column you can use SELECT '+STUFF(@col,1,1,'')+' instead of 'SELECT * part

answered Sep 24, 2016 at 7:10

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rishi Bharadwaj Over a year ago

Beauty, this is just awesome. Thanks a lot. Is there a possible way to remove rownumber from the last selection?

gofr1 Over a year ago

Of course you can! I add this to my answer, see the very end of it.

Rishi Bharadwaj Over a year ago

I just did the same. Thanks again. I have got to use temporary tables instead of table variables because the data in the xml string gonna be huge.

Roger Wolf · Accepted Answer · 2016-09-24 05:30:02Z

-1

You are on the right track. Only change you need is to select all elements instead of all attributes:

declare @x xml = '<root>
  <row1 invoice_number="WEDRT" vendor="Telekm" amount="233" status="1" created_date="42590" />
  <row2 invoice_number="MSFDRT" vendor="ARS" amount="344" status="1" created_date="42955" />
</root>';

select t.c.value('local-name(.)', 'sysname') as [NodeName],
    t.c.value('./@invoice_number', 'varchar(50)') as [InvoiceNumber],
    t.c.value('./@vendor', 'varchar(50)') as [Vender],
    t.c.value('./@amount', 'money') as [Amount],
    t.c.value('./@status', 'int') as [Status],
    t.c.value('./@created_date', 'int') as [CreatedDate]
from @x.nodes('/root[1]/*') t(c);

I have added the node name as the first column, in case you might need it. Remove it from the query if you don't.

answered Sep 24, 2016 at 5:30

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

1 Comment

Rishi Bharadwaj Over a year ago

Thanks but at run time I don't know the name of the columns so I can't hard code as invoice-number, vendor etc.

Collectives™ on Stack Overflow

Read xml with dynamic rows and attributes in sql server 2012

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related