SELECT

N1.x.value('(./NM1.03/NM1.03.1/text())[1]', 'NVARCHAR(256)') AS [NM1.03.1]

, N2.x.value('(./N4.01/N4.01.1/text())[1]', 'NVARCHAR(256)') AS [NM4.01.1]

, N3.x.value('(./NTE.02/NTE.02.1/text())[1]', 'NVARCHAR(256)') AS [NTE.02.1]

FROM

@x.nodes('/X12Transaction/NM1[position()= 3]') as N1(x)

, @x.nodes('/X12Transaction/N4[position()= 5]') as N2(x)

...

Test...

onclick:if_IFCode('', 'NVARCHAR(256)') AS [NM1.03.1]

, N2.x.value('(./N4.01/N4.01.1/text())[1]', 'NVARCHAR(256)') AS [NM4.01.1]

, N3.x.value('(./NTE.02/NTE.02.1/text())[1]', 'NVARCHAR(256)') AS [NTE.02.1]

FROM

@x.nodes('/X12Transaction/NM1[position()= 3]') as N1(x)

, @x.nodes('/X12Transaction/N4[position()= 5]') as N2(x)

...

I think I got it but want your advice if I am doing this correctly and if you would go about it the same way.

SELECT

N1.x.value('(./NM1.03/NM1.03.1/text())[1]', 'NVARCHAR(256)') AS [NM1.03.1]

,...

One other question. If I want to SELECT specific nodes / attributes, what am I doing wrong below?

DECLARE @x xml = '<X12Transaction elementDelimiter="*" segmentDelimiter=" " subelementDelimiter=">">

<ISA>

...

This is beautiful! Thank you very much. This will get me heading down the right path.

BTW - How do I put the code in a query window on SSC so its easier on the eyes?

Below are three different ways to identify what exists in TableA that does not exist in TableB. Pick your flavor.

--EXCEPT

SELECT * FROM TABLEA

EXCEPT

SELECT * FROM TABLEB

--LEFT OUTER JOIN

SELECT a.*...

Thanks everyone!

Jeff - I got some reading, coding, and testing this weekend! Thanks for the great posts.

Never played with CROSS Tab. Do you have an example I can play around with?

I looked into books online but not sure if I'm looking at the right thing here....

I redesigned the structure. The PIVOT function helped me out. Thanks all for your input!

Here is the sample data. I'm only using the four columns as an example. I will be needing more once I implement. If there is a more...

Thanks guys! I will read Jeff's article and post the data structure correctly.

For the sub select solution. This may work. Is there anyway to insert the...

I cant have hard coded values. I am looking to select the columns since the values will vary as different data is inserted into this table. Any other...

Thank you!

Thank you Loundy! Gail are you in agreement to shrink and rebuild indexes?