SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LOADING COMPLEX XML FILE


LOADING COMPLEX XML FILE

Author
Message
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
It doesn't get dumped anywhere, it is just a select query.

To insert the transformed xml into a table, just add an insert statement before the select i.e.


;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)
INSERT INTO SomeTable (SomeXmlColumn)
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/@d1p1BigGrinescriptor)}</FullName>
</Worker>
}
</Workers>
')


sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 245
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/@d1p1BigGrinescriptor)}</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/@d1p1BigGrinescriptor)}</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/@d1p1BigGrinescriptor)}</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.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
Ok, we don't need to transform the xml first via xquery if you only want to shred the xml to a flat table. from your original post it appeared that you wanted to transform the xml that you had to a new format..

this piece of code will load your file and then shred the two values out to a table


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

;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)
SELECT t.c.value('(d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Contingent_Worker_ID"]/text())[1]', 'int') as WorkerId
, t.c.value('(d1p1:Worker_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') as FullName
FROM @xml.nodes('//d1p1:Worker') t(c)



however I must ask that the filename you are loading has an xsd extension. normally this is used for xml schemas and not xml data. is this file an xml schema or does the file actually contain xml data like that you have posted previously.
sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 245
Thank you so much. I really don't know what to say but you have basically did this for me. I have ammended the query as a SELECT INTO to dump the data into a table but the EmployeeID was not been picked up. The column contain null throughout. Only the full name was picked. How can I ammend it to pick the corresponding EmployeeID for each FullName?

Thanks.

:-D
sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 245
I have ammended it as below and it is now picking EmployeeID as well. Thanks and God bless. I will give you a shout when I get to other section of the xml. Those are actually more complex than this because up to 20 fields are involved. Once again, thanks.

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

;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)
SELECT t.c.value('(d1p1:Worker_Reference/d1p1:ID[@d1p1:type="Employee_ID"]/text())[1]', 'int') as WorkerID
, t.c.value('(d1p1:Worker_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') as FullName
--INTO UserReference4
FROM @xml.nodes('//d1p1:Worker') t(c)
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
Hi.. I'm glad I could help Smile
sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 245
Hi Arthur,

Thanks for introducing me to XQuery. Unfortunately, my knowledged is not enough to handle the data that I am pulling. The next one that I am pulling is nested and has the same information repeated in the same section for a single worker.

The copied code for a single worker is pasted below:

- <d1p1:Worker>
- <d1p1:Worker_Data>
<d1p1:Worker_ID>14009</d1p1:Worker_ID>
- <d1p1Tongueersonal_Data>
- <d1p1:Name_Data>
- <d1p1:Legal_Name_Data>
- <d1p1:Name_Detail_Data d1p1:Reporting_Name="Ola, Ola" d1p1:Formatted_Name="Ola Ola">
- <d1p1:Country_Reference d1p1BigGrinescriptor="Brazil">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">BR</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">BRI</d1p1:ID>
</d1p1:Country_Reference>
- <d1p1Tonguerefix_Data>
- <d1p1:Title_Reference d1p1BigGrinescriptor="Mr.">
<d1p1:ID d1p1:type="WID">ba15d1e6bc1f4263982db59ac0e7ad21</d1p1:ID>
<d1p1:ID d1p1:type="Predefined_Name_Component_ID">Mr_Brazil</d1p1:ID>
</d1p1:Title_Reference>
</d1p1Tonguerefix_Data>
<d1p1:First_Name>Ola</d1p1:First_Name>
<d1p1:Last_Name>Ola</d1p1:Last_Name>
</d1p1:Name_Detail_Data>
</d1p1:Legal_Name_Data>
- <d1p1Tonguereferred_Name_Data>
- <d1p1:Name_Detail_Data d1p1:Reporting_Name="Ola, Ola" d1p1:Formatted_Name="Ola Ola">
- <d1p1:Country_Reference d1p1BigGrinescriptor="Brazil">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">BR</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">BRI</d1p1:ID>
</d1p1:Country_Reference>
- <d1p1Tonguerefix_Data>
- <d1p1:Title_Reference d1p1BigGrinescriptor="Mr.">
<d1p1:ID d1p1:type="WID">ba15d1e6bc1f4263982db59ac0e7ad21</d1p1:ID>
<d1p1:ID d1p1:type="Predefined_Name_Component_ID">Mr_Brazil</d1p1:ID>
</d1p1:Title_Reference>
</d1p1Tonguerefix_Data>
<d1p1:First_Name>Ola</d1p1:First_Name>
<d1p1:Last_Name>Handa</d1p1:Last_Name>
</d1p1:Name_Detail_Data>
</d1p1Tonguereferred_Name_Data>
</d1p1:Name_Data>
- <d1p1:Contact_Data>
- <d1p1:Address_Data d1p1:Effective_Date="1900-01-01" d1p1BigGrinefaulted_Business_Site_Address="true" d1p1:Formatted_Address="Level 8 Vibgyor Tower G Block C62 Bandra Kurla Complex Brasilia- 400 051 India">
- <d1p1:Country_Reference d1p1BigGrinescriptor="India">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">IN</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">IND</d1p1:ID>
</d1p1:Country_Reference>
<d1p1:Last_Modified>2013-02-19T10:20:48.415+00:00</d1p1:Last_Modified>
<d1p1:Address_Line_Data>Floor 8 Shad Tower</d1p1:Address_Line_Data>
<d1p1:Address_Line_Data>T Block s16 Inaolaji Shopping Complex</d1p1:Address_Line_Data>
<d1p1:Municipality>Manaus</d1p1:Municipality>
<d1p1Tongueostal_Code>400 051</d1p1Tongueostal_Code>
- <d1p1:Usage_Data d1p1Tongueublic="true">
- <d1p1:Type_Data d1p1Tonguerimary="true">
- <d1p1:Type_Reference d1p1BigGrinescriptor="Work">
<d1p1:ID d1p1:type="WID">1f27f250dfaa4724ab1e1617174281e4</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Type_ID">WORK</d1p1:ID>
</d1p1:Type_Reference>
</d1p1:Type_Data>
</d1p1:Usage_Data>
</d1p1:Address_Data>
- <d1p1:Address_Data d1p1:Effective_Date="2010-07-01" d1p1BigGrinefaulted_Business_Site_Address="false" d1p1:Formatted_Address="Flat 9886, La Guesepe Beverly Hill, Off Beverly Road East West- 144498 Brazil">
- <d1p1:Country_Reference d1p1BigGrinescriptor="Brazil">
<d1p1:ID d1p1:type="WID">c4f78be1a8f14da0ab49ce1162348a5e</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-2_Code">BR</d1p1:ID>
<d1p1:ID d1p1:type="ISO_3166-1_Alpha-3_Code">BRI</d1p1:ID>
</d1p1:Country_Reference>
<d1p1:Last_Modified>2013-02-24T19:46:36.41+00:00</d1p1:Last_Modified>
<d1p1:Address_Line_Data>Flat 9886, La Guesepe</d1p1:Address_Line_Data>
<d1p1:Address_Line_Data>Beverly Hill, Off Beverly Road</d1p1:Address_Line_Data>
<d1p1:Municipality>East West</d1p1:Municipality>
<d1p1Tongueostal_Code>144498</d1p1Tongueostal_Code>
- <d1p1:Usage_Data d1p1Tongueublic="false">
- <d1p1:Type_Data d1p1Tonguerimary="true">
- <d1p1:Type_Reference d1p1BigGrinescriptor="Home">
<d1p1:ID d1p1:type="WID">836cf00ef5974ac08b786079866c946f</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Type_ID">HOME</d1p1:ID>
</d1p1:Type_Reference>
</d1p1:Type_Data>
- <d1p1:Use_For_Reference d1p1BigGrinescriptor="Mailing">
<d1p1:ID d1p1:type="WID">9385649a0c4b4ddea7fb0d823c2f1bcd</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Behavior_ID">MAILING</d1p1:ID>
</d1p1:Use_For_Reference>
</d1p1:Usage_Data>
</d1p1:Address_Data>
- <d1p1:Email_Address_Data>
<d1p1:Email_Address>ola@sqlservercentral.com</d1p1:Email_Address>
- <d1p1:Usage_Data d1p1Tongueublic="true">
- <d1p1:Type_Data d1p1Tonguerimary="true">
- <d1p1:Type_Reference d1p1BigGrinescriptor="Work">
<d1p1:ID d1p1:type="WID">1f27f250dfaa4724ab1e1617174281e4</d1p1:ID>
<d1p1:ID d1p1:type="Communication_Usage_Type_ID">WORK</d1p1:ID>
</d1p1:Type_Reference>
</d1p1:Type_Data>
</d1p1:Usage_Data>
</d1p1:Email_Address_Data>
</d1p1:Contact_Data>
<d1p1:Tobacco_Use>false</d1p1:Tobacco_Use>
</d1p1Tongueersonal_Data>
</d1p1:Worker_Data>
</d1p1:Worker>



Part of the nesting as I understand is

<Worker>
<Worker_Data>
<Worker_ID>91000</Worker_ID>
<Personal_Data>
<Name_Data>
.
.
.
.


I wrote the code below but it only return null values

DECLARE @xml XML
SELECT @xml = (SELECT * FROM OPENROWSET(BULK 'd:\chamila\XMLTest\Personal1.xml', SINGLE_BLOB) x)

;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)
SELECT t.c.value('(d1p1:Worker_ID)[1]', 'int') as EmployeeID
, t.c.value('(d1p1:Name_Detail_Data/@d1p1:Reporting_Name)[1]', 'varchar(100)') as FullName
, t.c.value('(d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') as CountryISOCode
INTO UserReference5
FROM @xml.nodes('//d1p1:Worker') t(c)


I thought it might be because I didnt wrapped (//Worker_Data/Worker_ID) them as they appear down the nesting.

I read your blogs and I understand how to do this with normal xml but not with the complex ones that I am dealing with. Could you please, advise me on how to do this?

Thanks.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
Hi. when you are specifying xpaths (that is the xml path to the node you want), you need to specify either the full path to the xml node that you want, or use the // to search anywhere from that point or lower in the xml. Personally i prefer to always be explicit and specify the full path to the node that i want instead of using //. this is because I want to be sure i am getting the node that i want and not run the risk of hitting an identically named node from elsewhere in the xml document.

In the the query that we working on, the <Worker> node is the starting point as what is specified in the nodes() function. so in your query you need to specify an xpath from that starting point in the xml and you need to specify each level in the xml to get to the node that you want.

so for example:


;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)
SELECT t.c.value('(d1p1:Worker_Data/d1p1:Worker_ID)[1]', 'int') as EmployeeID
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/@d1p1:Reporting_Name)[1]', 'varchar(100)') as FullName
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') as CountryISOCode
FROM @xml.nodes('//d1p1:Worker') t(c)


sahoong
sahoong
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 245
Hi Arthur,

Just stopped by to say 'A BIG THANK YOU'. You broke this down to me and introduced me to XQuery. This is my query now and it is working perfectly:


DECLARE @xml XML
SELECT @xml = (SELECT * FROM OPENROWSET(BULK 'd:\chamila\XMLTest\Personal1.xml', SINGLE_BLOB) x)

;with xmlnamespaces ('urn:com.workday/bsvc' as d1p1)
SELECT t.c.value('(d1p1:Worker_Data/d1p1:Worker_ID)[1]', 'int') as EmployeeID
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') AS CountryISOCode
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/@d1p1:Formatted_Name)[1]', 'varchar(100)') AS [Full Name]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1Tonguerefix_Data/d1p1:Title_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') AS Title
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:First_Name)[1]', 'varchar(100)') AS [First Name]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Last_Name)[1]', 'varchar(100)') AS [Last Name]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[1]', 'varchar(100)') AS [Address Line 1]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[2]', 'varchar(100)') AS [Address Line 2]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Municipality)[1]', 'varchar(100)') City
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1Tongueostal_Code)[1]', 'varchar(100)') [Postal Code]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') AS Country
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[2]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Municipality)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1Tongueostal_Code)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') AS [Work Address]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Last_Modified)[1]', 'varchar(100)') AS [Last Modified]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[1]', 'varchar(100)') AS [Address Line 3]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[2]', 'varchar(100)') AS [Address Line 4]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Municipality)[1]', 'varchar(100)') [City 2]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1Tongueostal_Code)[1]', 'varchar(100)') [Postal Code 2]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') AS [Country 2]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[2]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Municipality)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1Tongueostal_Code)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Country_Reference/@d1p1BigGrinescriptor)[1]', 'varchar(100)') AS [Home Address]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Last_Modified)[1]', 'varchar(100)') AS [Last Modified 2]
, t.c.value('(d1p1:Worker_Data/d1p1Tongueersonal_Data/d1p1:Contact_Data/d1p1:Email_Address_Data/d1p1:Email_Address)[1]', 'varchar(100)') AS [Email Address]
INTO Personal1
FROM @xml.nodes('//d1p1:Worker') t(c)


Hope it could help someone else as well.

Once again, THANKS so much.
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3184 Visits: 1779
No problem.. thank you for the nice feedback.. Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search