Read XML data into SQL Server table

Question

I have xml like this (it contains more than 300k):

<?xml version="1.0" encoding="utf-8"?>
<data>
  <kitap>
    <Adi>Matematik +5 Yaş</Adi>
    <Barkod>9786052342046</Barkod>
    <Resim>http://xxxx.com.tr/Icerik/Gorsel/Urun/9786052342046.jpg</Resim>
  </kitap>
  <kitap>
    <Adi>Broke - Light (Ciltli)</Adi>
    <Barkod>9786057944085</Barkod>
    <Resim>http://xxx.com.tr/Icerik/Gorsel/Urun/9786057944085.jpg</Resim>
  </kitap>
</data>

I want to store data into a database table.

Here is my T-SQL code:

INSERT INTO ddd (Adi,Barkod,Resim) 
    SELECT 
        X.kitap.query('Adi').value('.', 'nvarchar(1000)'),
        X.kitap.query('Barkod').value('.', 'nvarchar(1000)'),
        X.kitap.query('Resim').value('.', 'nvarchar(1000)')
    FROM 
        (SELECT 
             CAST(x AS XML)
         FROM 
             OPENROWSET(BULK 'C:\sil\b.xml', SINGLE_BLOB) AS T(x)
        ) AS T(x)
CROSS APPLY 
    x.nodes('data/kitap') AS X(kitap);

but I get this error:

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.

What is the structure of your database table ddd ?? What are the columns, and what is their datatype? — marc_s
– marc_s, Commented Nov 22, 2018 at 20:41

Gottfried Lesigang · Accepted Answer · 2018-11-23 07:56:50Z

2

Well, your XML-query is fine, although I'd simplify it a bit:

DECLARE @xml XML=
'<?xml version="1.0" encoding="utf-8"?>
<data>
  <kitap>
    <Adi>Matematik +5 Yaş</Adi>
    <Barkod>9786052342046</Barkod>
    <Resim>http://xxxx.com.tr/Icerik/Gorsel/Urun/9786052342046.jpg</Resim>
  </kitap>
  <kitap>
    <Adi>Broke - Light (Ciltli)</Adi>
    <Barkod>9786057944085</Barkod>
    <Resim>http://xxx.com.tr/Icerik/Gorsel/Urun/9786057944085.jpg</Resim>
  </kitap>
</data>';

SELECT 
    X.kitap.value('(Adi/text())[1]', 'nvarchar(1000)'),
    X.kitap.value('(Barkod/text())[1]', 'nvarchar(1000)'),
    X.kitap.value('(Resim/text())[1]', 'nvarchar(1000)')
FROM @xml.nodes('data/kitap') AS X(kitap);

The reason for the error mentioned ("String or binary data would be truncated") is with the length of your target fields assumably. You are reading all three columns as nvarchar(1000). Use the following to find the max lengths per column:

SELECT 
    MAX(LEN(X.kitap.value('(Adi/text())[1]', 'nvarchar(max)'))) AS MaxLenAdi,
    MAX(LEN(X.kitap.value('(Barkod/text())[1]', 'nvarchar(max)'))) AS MaxLenBarkod,
    MAX(LEN(X.kitap.value('(Resim/text())[1]', 'nvarchar(max)'))) AS MaxLenResim
FROM @xml.nodes('data/kitap') AS X(kitap);

Now check your ddd table's definition, if the values will fit into 1) nvarchar(1000) and 2) into the table's columns.

One more thing to mention: Your XML-file is claiming to be utf-8 encoded. Reading this simply as SINGLE_BLOB might be dangerous... This will call the file as a byte-stream and will take each single byte as a character. But utf-8 uses multi-byte codes to encode all the characters existing in the world. This will either lead to garbage data or to an error.

Try to import this as SINGLE_CLOB (Character LOB - not secure, but a bit better)
Try to import this with utf-8 support (I think this is available since v2014 SP1)
Use an external tool to convert your file to utf-16 (even better: ucs-2)

And one final hint: Very often XML-files claim to have a special encoding (first line declaration <?xml ... encoding="xyz"?>. Very often people just do not know what this is and think, that this - hoewever - must be there ("Uhm... Don't know... Just copied the template..."). It might be worth to check the file's actual encoding. In general it is a good advise to omit this declaration entirely within SQL-Server.

answered Nov 23, 2018 at 7:56

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

user1688401 Over a year ago

reading file take too many time ...here is my t-sql INSERT INTO ddd (Adi,Barkod,Resim) SELECT X.kitap.query('Adi').value('.', 'nvarchar(1000)'), X.kitap.query('Barkod').value('.', 'nvarchar(100)'), X.kitap.query('Resim').value('.', 'nvarchar(Max)') FROM ( SELECT CAST(x AS XML) FROM OPENROWSET( BULK 'C:\sil\d.xml', utf-8) AS T(x) ) AS T(x) CROSS APPLY x.nodes('data/kitap') AS X(kitap);

Gottfried Lesigang Over a year ago

@user1688401 Please avoid follow-up questions, especially when they are not related to the initial question. Rather start a new question. You might place a link to this one, if you think it helps. New questions pull the highest attraction :-) One last hint: "takes to much time" is not specific enough. You must describe in detail, which part is time consuming (how big is the file, measure the time for simple reading, find out, where the time is going...

Collectives™ on Stack Overflow

Read XML data into SQL Server table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related