Htykuut

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:silb.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.

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.