Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

LOADING COMPLEX XML FILE Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 2:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
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/@d1p1:Descriptor)}</FullName>
</Worker>
}
</Workers>
')

Post #1469900
Posted Wednesday, July 3, 2013 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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.
Post #1470168
Posted Wednesday, July 3, 2013 12:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
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/@d1p1:Descriptor)[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.
Post #1470193
Posted Thursday, July 4, 2013 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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.

Post #1470352
Posted Thursday, July 4, 2013 3:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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/@d1p1:Descriptor)[1]', 'varchar(100)') as FullName
--INTO UserReference4
FROM @xml.nodes('//d1p1:Worker') t(c)
Post #1470363
Posted Thursday, July 4, 2013 3:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
Hi.. I'm glad I could help :)
Post #1470366
Posted Thursday, July 4, 2013 11:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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>
- <d1p1:Personal_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 d1p1:Descriptor="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:Prefix_Data>
- <d1p1:Title_Reference d1p1:Descriptor="Mr.">
<d1p1:ID d1p1:type="WID">ba15d1e6bc1f4263982db59ac0e7ad21</d1p1:ID>
<d1p1:ID d1p1:type="Predefined_Name_Component_ID">Mr_Brazil</d1p1:ID>
</d1p1:Title_Reference>
</d1p1:Prefix_Data>
<d1p1:First_Name>Ola</d1p1:First_Name>
<d1p1:Last_Name>Ola</d1p1:Last_Name>
</d1p1:Name_Detail_Data>
</d1p1:Legal_Name_Data>
- <d1p1:Preferred_Name_Data>
- <d1p1:Name_Detail_Data d1p1:Reporting_Name="Ola, Ola" d1p1:Formatted_Name="Ola Ola">
- <d1p1:Country_Reference d1p1:Descriptor="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:Prefix_Data>
- <d1p1:Title_Reference d1p1:Descriptor="Mr.">
<d1p1:ID d1p1:type="WID">ba15d1e6bc1f4263982db59ac0e7ad21</d1p1:ID>
<d1p1:ID d1p1:type="Predefined_Name_Component_ID">Mr_Brazil</d1p1:ID>
</d1p1:Title_Reference>
</d1p1:Prefix_Data>
<d1p1:First_Name>Ola</d1p1:First_Name>
<d1p1:Last_Name>Handa</d1p1:Last_Name>
</d1p1:Name_Detail_Data>
</d1p1:Preferred_Name_Data>
</d1p1:Name_Data>
- <d1p1:Contact_Data>
- <d1p1:Address_Data d1p1:Effective_Date="1900-01-01" d1p1:Defaulted_Business_Site_Address="true" d1p1:Formatted_Address="Level 8 Vibgyor Tower G Block C62 Bandra Kurla Complex Brasilia- 400 051 India">
- <d1p1:Country_Reference d1p1:Descriptor="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>
<d1p1:Postal_Code>400 051</d1p1:Postal_Code>
- <d1p1:Usage_Data d1p1:Public="true">
- <d1p1:Type_Data d1p1:Primary="true">
- <d1p1:Type_Reference d1p1:Descriptor="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" d1p1:Defaulted_Business_Site_Address="false" d1p1:Formatted_Address="Flat 9886, La Guesepe Beverly Hill, Off Beverly Road East West- 144498 Brazil">
- <d1p1:Country_Reference d1p1:Descriptor="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>
<d1p1:Postal_Code>144498</d1p1:Postal_Code>
- <d1p1:Usage_Data d1p1:Public="false">
- <d1p1:Type_Data d1p1:Primary="true">
- <d1p1:Type_Reference d1p1:Descriptor="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 d1p1:Descriptor="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 d1p1:Public="true">
- <d1p1:Type_Data d1p1:Primary="true">
- <d1p1:Type_Reference d1p1:Descriptor="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>
</d1p1:Personal_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/@d1p1:Descriptor)[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.


Post #1470490
Posted Thursday, July 4, 2013 1:56 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
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/d1p1:Personal_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/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') as CountryISOCode
FROM @xml.nodes('//d1p1:Worker') t(c)


Post #1470503
Posted Monday, July 8, 2013 5:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 12:03 PM
Points: 112, Visits: 240
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/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS CountryISOCode
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_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/d1p1:Personal_Data/d1p1:Name_Data/d1p1:Legal_Name_Data/d1p1:Name_Detail_Data/d1p1:Prefix_Data/d1p1:Title_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS Title
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_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/d1p1:Personal_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/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[1]', 'varchar(100)') AS [Address Line 1]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[2]', 'varchar(100)') AS [Address Line 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Municipality)[1]', 'varchar(100)') City
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Postal_Code)[1]', 'varchar(100)') [Postal Code]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS Country
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Address_Line_Data)[2]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Municipality)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Postal_Code)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS [Work Address]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data/d1p1:Last_Modified)[1]', 'varchar(100)') AS [Last Modified]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_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/d1p1:Personal_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/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Municipality)[1]', 'varchar(100)') [City 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Postal_Code)[1]', 'varchar(100)') [Postal Code 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS [Country 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Address_Line_Data)[2]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Municipality)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Postal_Code)[1]', 'varchar(100)') + ' ' + ',' + ' ' +
t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Country_Reference/@d1p1:Descriptor)[1]', 'varchar(100)') AS [Home Address]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_Data/d1p1:Contact_Data/d1p1:Address_Data[2]/d1p1:Last_Modified)[1]', 'varchar(100)') AS [Last Modified 2]
, t.c.value('(d1p1:Worker_Data/d1p1:Personal_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.
Post #1471124
Posted Monday, July 8, 2013 2:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
No problem.. thank you for the nice feedback.. :)
Post #1471361
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse