Skip to Content
0
Feb 13, 2013 at 08:24 PM

Scenario with external xml

16 Views

Hi,

My english is very bad, sorry.

I have 2 SP in the data base

The first save a xml in a specific directory.

DECLARE           @ID          NVARCHAR(255)          ,@SYS          NVARCHAR(255)          ,@MesN          VARCHAR(2)          ,@AnioN          VARCHAR(4)          ,@Nom          NVARCHAR(255)SELECT            @MesN=CASE LEN(@Mes) WHEN 1 THEN '0'+CAST(@Mes AS VARCHAR(2)) WHEN 2 THEN CAST(@Mes AS VARCHAR(2)) END          ,@AnioN=CAST(@Anio AS VARCHAR(4))          ,@SYS=(SELECT T3.ParamValue FROM [SBO-COMMON]..SLSPP T3 WHERE T3.CompID = (SELECT T2.CompID FROM [SBO-COMMON]..SLSP T2          WHERE T2.CompIdent = (SELECT T1.dbName FROM [SBO-COMMON]..SRGC T1 WHERE T1.cmpName = (SELECT T0.CompnyName FROM OADM T0))))          ,@Nom=(SELECT CAST(DATEPART(YEAR,SYSDATETIME()) AS CHAR(4)) + CAST(CASE LEN(DATEPART(MONTH, SYSDATETIME())) WHEN 1 THEN '0'+CAST(DATEPART(MONTH, SYSDATETIME()) AS CHAR(1)) WHEN 2 THEN CAST(DATEPART(MONTH, SYSDATETIME()) AS CHAR(2)) END AS CHAR(2)) + CAST(DATEPART(DAY, SYSDATETIME()) AS CHAR(2)) + CAST(DATEPART(HOUR, SYSDATETIME()) AS CHAR(2)) + CAST(DATEPART(MINUTE, SYSDATETIME()) AS CHAR(2)) + CAST(DATEPART(SECOND, SYSDATETIME()) AS CHAR(2)) + CAST(DATEPART(NANOSECOND, SYSDATETIME()) AS NVARCHAR(10)))SELECT @ID ='BCP "SELECT DISTINCT '''+ @Libro +''' AS Libro,'''+ @MesN +''' AS Mes,'''+ @AnioN +''' AS Anio, '''+ @SYS +''' AS SysID FROM SBOMERSUD..OADM Row FOR XML AUTO, ROOT(''LCLV''), ELEMENTS" ' +'QUERYOUT "C:\ISICAT\In\LL\Libro.xml" -S RUBENLOBOS -T -w -r -t'EXEC xp_cmdshell @ID

This is the file

The structure is

 <?xml version="1.0"?>-<LCLV>-     <Row>          <Libro>LV</Libro>          <Mes>01</Mes>          <Anio>2013</Anio>          <SysID>0010000106</SysID>     </Row></LCLV>

Exists in B1if, a File System, that system monitoring this location, and trigger one scenario.

My problem is the next.

In the xpath

<xsl:variable name="FileName">

<xsl:value-of select="/vpf:Msg/vpf:Header/vpf:Inbound/@file"/>

.

<xsl:value-of select="/vpf:Msg/vpf:Header/vpf:Inbound/@ext"/>

</xsl:variable>

<Script>EXEC SBOINVXML <xsl:value-of select="document('$FileName')/LCLV/Row/Libro"/></Script>

Nothing appears, just EXEC SBOINVXML.

¿How I could load data from libro.xml to this scenario?

Regards.

Attachments

ScreenShot078.png (27.3 kB)
ScreenShot079.png (44.0 kB)