Hello. Thanks for your help. Some section of the codes didnt work but I have been reading your blog and some other articles on xquery. I will tell you what works and what didnt as well as my plan, moving forward

Number 1

DECLARE @xml XML

SELECT @xml = (SELECT * FROM OPENROWSET(BULK 'D:\chamila\XMLTest\text1.xsd', SINGLE_BLOB) x)

This works - It gives:

"Command(s) completed successfully"

Number 2

DECLARE @xml XML;

WITH xmlnamespaces ('urn:com.workday/bsvc' as d1p1)

SELECT @xml.query('

<Workers>

{

for $x in //d1p1:Worker

return

<Worker>

<EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

<FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

</Worker>

}

</Workers>

')

This gives:

(No column name)

NULL

Number 3

WITH xCTE AS

(

SELECT @xml.query('

<Workers>

{

for $x in //d1p:Worker

return

<Worker>

<EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

<FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

</Worker>

}

</Workers>

') AS DocXml

)

SELECT t.c.value('(Value/text())[1]', 'varchar(10)') AS 'EmployeeID'

, t.c.value('(PrevValue/text())[1]', 'varchar(10)') AS 'FullName'

, t.c.value('(NextValue/text())[1]', 'varchar(10)') AS 'NextSomeElementValue'

FROM xCTE

CROSS APPLY DocXml.nodes('/Data/SomeElement') AS T(c);

This gives:

Msg 257, Level 16, State 3, Line 2

Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query

The above is understandable because the table that I am trying to insert it into has EmployeeID declared as a varchar column.

I thought the above is pulling two columns but when I edited the above to include 2 columns as in

INSERT INTO UserReference2 (EmployeeID, FullName), I got the message below

Msg 120, Level 15, State 1, Line 3

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns. Why is this? I thought that there are 2 elements - EmployeeID and FullName.

I have rewritten the query using the CTE that I saw on your blog. This is very close to what I want as I want to store value not xml. So, I have rewritten it as below:

DECLARE @xml XML;

WITH xmlnamespaces ('urn:com.workday/bsvc' as d1p1);

WITH xCTE AS

(

SELECT @XML.query('

<Workers>

{

for $x in //d1p:Worker

return

<Worker>

<EmployeeID>{$x/d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text()}</EmployeeID>

<FullName>{data($x/d1p1:Worker_Reference/@d1p1:Descriptor)}</FullName>

</Worker>

}

</Workers>

') AS DocXml

)

SELECT t.c.value('(Value/text())[1]', 'varchar(10)') AS 'EmployeeID'

, t.c.value('(PrevValue/text())[1]', 'varchar(10)') AS 'FullName'

INTO UserReference2

FROM xCTE

CROSS APPLY DocXml.nodes('/Data/SomeElement') AS T(c);

However, it is given me syntax error and the error is pointing to between the second and third line. What can I do to make this work?

Many thanks in advance.