Good day to all!

In MSSQL, I created a procedure (Function1) that pushes xml content, and it already drives it into a table.

EXEC [master].[dbo].sp_xml_preparedocument @DocID OUTPUT, @xml SELECT * FROM openxml(@DocID, 'bla/bla', 2)

On php I call this procedure.

 $fileContent = file_get_contents(iconv('utf-8', 'cp1251', $fileName)); $data = Yii::app()->db->createCommand('EXEC Function1 :xml')->bindValue('xml', $fileContent)->queryRow();

Well, I get, actually, such an error:

SQLSTATE[IMSSP]: An error occurred translating string for input param 1 to UCS-2

Who-thread faced with a similar error?

  • Judging by the text of the error can not convert the contents of the file specified in $fileName to UCS-2 (UTF-16). - i-one

1 answer 1

Good day.

  1. Try to refuse at all this sp_xml_preparedocument. This was done in the days of MS SQL 2000. Now they work with XML like this (I had to make a screen, because tags in the code prevent me from inserting it here): An example of working with Xml in MS SQL or (if the first link does not open), then it is the same: screen on picshare

  2. At procedure which you cause from the client, do parameter of type @NVarChar (Max).

Next, the code that will not look normal because of the tags. Normal view - see attachment in the form of a snapshot from the screen.

 DECLARE @StringData NVarChar(Max) = N'<Root><Record Attribute1="Value1" Attribute2="2" Attribute3="2016-12-31"></Record></Root>', @XmlData Xml = N' <Root> <Record Attribute1="Value1.1" Attribute2="2" Attribute3="2016-12-31"/> <Record Attribute1="Value1.2" Attribute2="22" Attribute3="2016-10-20"/> </Root>', @XmlData2 Xml = N' <Root> <Record> <Field1>Value1</Field1> <Field1>Value1+</Field1> <Field2 Sign="false">111</Field2> <Field2 Sign="true">999</Field2> <Field3 SubAttribute="18+">2016-12-31</Field3> </Record> </Root>' DECLARE @Data Xml = @StringData -- даже необзательно использовать CAST(@StringData AS Xml) SELECT -- Запрос 1 [Attribute1] = Data.Record.value('@Attribute1', 'NVarChar(100)'), [Attribute2] = Data.Record.value('@Attribute2', 'Int'), [Attribute3] = Data.Record.value('@Attribute3', 'Date') -- Здесь необязательно использовать [N] у атрибута (как правило N = 1) FROM @Data.nodes('Root/Record') AS Data(Record) SELECT -- Запрос 2: аналогично Запросу 1 [Attribute1] = Data.Record.value('@Attribute1[1]', 'NVarChar(100)'), [Attribute2] = Data.Record.value('@Attribute2[1]', 'Int'), [Attribute3] = Data.Record.value('@Attribute3[1]', 'Date') FROM @XmlData.nodes('Root/Record') AS Data(Record) SELECT -- Запрос 3 - данные из узлов [Attribute1] = Data.Record.value('Field1[2]', 'NVarChar(100)'), -- А для узлов [N] - номер узла удовлетворяющий условиям! [Attribute2] = Data.Record.value('Field2[@Sign="true"][1]', 'Int'), -- Здесь: дай первый узел, у которого есть атрибут Sign со значением true [Attribute3] = Data.Record.value('Field3[1]', 'Date'), [SubAttribute3] = Data.Record.value('Field3[1]/@SubAttribute[1]', 'NVarChar(50)') FROM @XmlData2.nodes('Root/Record') AS Data(Record)
  • And yet paste the code text. If anything, we will correct. - Alexander Petrov
  • So send the stream from the file to the parameter of type NVarChar! In the same place in question, as I saw: createCommand ('EXEC Function1: xml') -> bindValue ('xml', $ fileContent) It will just be something like this: createCommand ('EXEC Function1: Data') -> bindValue (' Data ', $ fileContent). There is an assumption that in the parameter of the type NVarChar (Max) the contents from the file will most likely be good, i.e. It seems to me that the problem arose here because the access component that you are using in PHP could not be put in xml - VG
  • 2
    For the text to be perceived as a code, you need four spaces in the beginning. You can use a special button in the interface with curly braces: {} . - Alexander Petrov
  • one
    Well, of course, I used the "{}" button. And then Ctrl + V. Just because of the presence of tags, I suspect the view broke. Now I look - corrected - VG
  • one
    @VG must first insert the code, and then select it and press {}. Or simply insert the code indented 4 spaces in advance (or 8, if inside the list) - ottablirovat still in the studio - PashaPash ♦