sql server xml.value skeleton dynamic cases

Question

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.

Answer 1

You can use nodes() to shred the XML into rows on POSDetails element level, then use value() to extract the inner text of PSTATION and INVNUMBER as NVARCHAR data, something like this :

INSERT INTO @Emp(ps,inv)
SELECT 
    P.value('(PSTATION)[1]', 'NVARCHAR(MAX)'),
    P.value('(INVNUMBER)[1]', 'NVARCHAR(MAX)')
FROM @Xml.nodes('/NewDataSet/POSDetails') AS T(P)