jeudi 19 mai 2016

sql server xml.value skeleton dynamic cases

I'm using SQL Server 2005.I want to enquiry xml records to row by rows. So that I was used the below codes. There is three records. When I excuted like that, I can see the one records only.

DECLARE @Xml XML

DECLARE @Emp TABLE(ps NVARCHAR(MAX) NULL,inv NVARCHAR(MAX) NULL)


INSERT INTO @Emp(ps,inv)

SELECT  @Xml.value('(/NewDataSet/POSDetails/PSTATION)[1]', 'NVARCHAR(MAX)'),

@Xml.value('(/NewDataSet/POSDetails/INVNUMBER)[1]', 'NVARCHAR(MAX)')

SELECT * FROM @Emp

So that, I use next two SQL Statements the above SQL. I want to use dynamic for index.Can I use? Please advise.

@Xml.value('(/NewDataSet/POSDetails/PSTATION)[2]/[3]', 'NVARCHAR(MAX)')

INSERT INTO @Emp(ps,inv)

SELECT @Xml.value('(/NewDataSet/POSDetails/PSTATION)[2]', 'NVARCHAR(MAX)'), @Xml.value('(/NewDataSet/POSDetails/INVNUMBER)[2]', 'NVARCHAR(MAX)')

INSERT INTO @Emp(ps,inv) SELECT @Xml.value('(/NewDataSet/POSDetails/PSTATION)[3]', 'NVARCHAR(MAX)'), @Xml.value('(/NewDataSet/POSDetails/INVNUMBER)[3]', 'NVARCHAR(MAX)')

Thank in advance.

Aucun commentaire:

Enregistrer un commentaire