CODEWITHSUNDEEP
sql-serverxml
wicherqm
wicherqm

Reputation: 245

Dynamic xml to SQL Server table

I have dynamic generated XML files

-----Example1-----
<items>
<param1>aaaa</param1>
<param2>bbbb</param2>
<param3>cccc</param3>
</items>
-----Example2-----
<items>
<test1>dddd</test1>
<test7>eeee</test7>
<john1>ffff</john1>
<john2>ffff</john2>
</items>

I want to convert this xml files to table like this

-----SQL TABLE for Example1-----
Name     Value
param1   aaaa
param2   bbbb
param3   cccc
-----SQL TABLE for Example2-----
Name     Value
test1   dddd
test7   eeee
john1   ffff
john2   ffff

The problem - items xml tags names are different in each xml file (like in sample) - item numbers is different in each xml file

Anyone have any idea

Update1 Sample in C# that i have done but i need to do this in T-SQL :(

  public static void test()
    {

        string test = @"
        <items>
        <param1>aaaa</param1>
        <param2>bbbb</param2>
        <param3>cccc</param3>
        </items>
        ";

        XmlDocument newdoc = new XmlDocument();
        XmlNode root = newdoc.CreateElement("dataset");
        newdoc.AppendChild(root);
        XmlDocument doc = new XmlDocument();
        doc.InnerXml = test;
        XmlNodeList lst = doc.SelectNodes("//items/*"); 
        foreach (XmlNode item in lst)
        {
            Console.WriteLine(item.Name + ": " + item.InnerXml);
        }
    }

RESULT param1: aaaa param2: bbbb param3: cccc

UPDATE2 partialy resolved i need only get xml tag name

declare @foo xml
set @foo = N'
<items>
       <param1>aaaa</param1>
       <param2>bbbb</param2>
       <param3>cccc</param3>
</items>'

SELECT
'' as name,  --?? no idea how to get names param1,param2,param3
bar.value('./.','VARCHAR(14)')  as value 
FROM
@foo.nodes('/items/*') AS foo(bar)

Upvotes: 2

Views: 10481

Answers (2)

suraj thakur
suraj thakur

Reputation: 81

--Convert Dynamically XML Into SQL Server

    Declare @XMLFile XML='<xml xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
      <x:PivotCache>
        <x:CacheIndex>1</x:CacheIndex>
        <rs:data>
          <z:row Col1="Name" Col2="Address"/>
          <z:row Col1="Sanju" Col2="Goa"/>
          <z:row Col1="Sonu" Col2="Mumbai" />
              </rs:data>
      </x:PivotCache>
    </xml>'

    ;WITH XMLNAMESPACES('urn:schemas-microsoft-com:office:excel' AS x,
        'urn:schemas-microsoft-com:rowset' AS rs,
        '#RowsetSchema' AS z)

         SELECT 
         m.c.value('local-name(.)', 'nvarchar(500)') AS NodeName,
         m.c.value('(.)[1]','nvarchar(1000)') AS [Val]
         FROM @XMLFile.nodes('//x:PivotCache/rs:data/z:row/@*') AS m(c)

Upvotes: 2

Darrel Miller
Darrel Miller

Reputation: 142014

It would be relatively easy to create an XSLT transform to convert the XML data into a set of INSERT INTO statements. Then you could just execute those statements. How useful that approach would be depends on how much data and how fast you need to import it.

You could even get the XSLT to do the CREATE TABLE DDL also, if you don't have tables already.

UPDATE: Using your code, you can use the local-name() function:

declare @foo xml
set @foo = N'
<items>
       <param1>aaaa</param1>
       <param2>bbbb</param2>
       <param3>cccc</param3>
</items>'

SELECT
bar.value('local-name(.)','VARCHAR(14)') as name,  
bar.value('./.','VARCHAR(14)')  as value 
FROM
@foo.nodes('/items/*') AS foo(bar)

Upvotes: 2

Related Questions