Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 XMl to temp Table

Author  Topic 

raghuveer125
Constraint Violating Yak Guru

285 Posts
Posted - 2011-05-24 : 08:34:21
Hi I want to insert this xml to temp table. Here this is not showing format correct so please copy and pest in notepad++

<?xml version="1.0" encoding="windows-1252"?>
<Message xmlns="http://www.ncpdp.org/schema/SCRIPT" version="008" release="001">
<Header>
<To Qualifier="ZZZ">T00000000021041</To>
<From Qualifier="ZZZ">S00000000000001</From>
<MessageID>4fdc7673b33541f8b4d4c69fbceb8781</MessageID>
<SentTime>2011-05-24T05:33:57</SentTime>
<Security>
<UsernameToken>
<Username/>
</UsernameToken>
<Sender>
<SecondaryIdentification>UMK95F3QYH</SecondaryIdentification>
<TertiaryIdentification>S00000000000001</TertiaryIdentification>
</Sender>
<Receiver>
<SecondaryIdentification/>
</Receiver>
</Security>
<TestMessage>1</TestMessage>
</Header>
<Body>
<RxHistoryResponse>
<Response>
<Approved/>
</Response>
<Prescriber>
<Identification>
<NPI>1313131312</NPI>
</Identification>
<Name>
<LastName>S</LastName>
<FirstName>Jagadish</FirstName>
</Name>
<Address>
<AddressLine1>Add1</AddressLine1>
<City>city</City>
</Address>
</Prescriber>
<Patient>
<Name>
<LastName>Bach</LastName>
<FirstName>Hiram</FirstName>
</Name>
<Address>
<AddressLine1>729 LOBSTER DRIVE</AddressLine1>
<City>WOODS HOLE</City>
<State>MA</State>
<ZipCode>02543</ZipCode>
</Address>
</Patient>
<BenefitsCoordination>
<EffectiveDate>2010-05-25</EffectiveDate>
<ExpirationDate>2011-05-24</ExpirationDate>
<Consent>Y</Consent>
</BenefitsCoordination>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>17.000</Value>
</Quantity>
<DaysSupply>20</DaysSupply>
<Directions>2 PUFFS 4 TIMES A DAY AS NEEDED</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>1</Quantity>
</Refills>
<WrittenDate>2011-05-20</WrittenDate>
<LastFillDate>2011-05-20</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>UNKNOWN</MutuallyDefined>
<DEANumber>BH1716577</DEANumber>
</Identification>
<Name>
<LastName>HATEM</LastName>
<FirstName>DAVID</FirstName>
<MiddleName>Y</MiddleName>
</Name>
<Address>
<AddressLine1>UMASS MED CTR 55 LAKE AVE</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016550000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088562731</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5088561769</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>90.000</Value>
</Quantity>
<DaysSupply>30</DaysSupply>
<Directions>TAKE 1 TABLET 3 TIMES A DAY</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>1</Quantity>
</Refills>
<WrittenDate>2011-04-16</WrittenDate>
<LastFillDate>2011-04-16</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>1</MutuallyDefined>
<DEANumber>BF2820199</DEANumber>
<StateLicenseNumber>.</StateLicenseNumber>
</Identification>
<Name>
<LastName>FAHEY</LastName>
<FirstName>DAVID</FirstName>
<MiddleName>A</MiddleName>
</Name>
<Address>
<AddressLine1>26 JULIO DR</AddressLine1>
<City>SHREWSBURY</City>
<State>MA</State>
<ZipCode>015450000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088425594</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5088420989</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>16.000</Value>
</Quantity>
<DaysSupply>25</DaysSupply>
<Directions>USE AS DIRECTED</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>5</Quantity>
</Refills>
<WrittenDate>2011-03-20</WrittenDate>
<LastFillDate>2011-03-20</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>1</MutuallyDefined>
<DEANumber>BF2820199</DEANumber>
<StateLicenseNumber>.</StateLicenseNumber>
</Identification>
<Name>
<LastName>FAHEY</LastName>
<FirstName>DAVID</FirstName>
<MiddleName>A</MiddleName>
</Name>
<Address>
<AddressLine1>26 JULIO DR</AddressLine1>
<City>SHREWSBURY</City>
<State>MA</State>
<ZipCode>015450000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088425594</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5088420989</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>60.000</Value>
</Quantity>
<DaysSupply>30</DaysSupply>
<Directions>TAKE 1 TABLET TWICE DAILY WITH MEALS</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>5</Quantity>
</Refills>
<WrittenDate>2011-02-18</WrittenDate>
<LastFillDate>2011-02-18</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>1</MutuallyDefined>
<DEANumber>AS5563386</DEANumber>
<StateLicenseNumber>99999999999</StateLicenseNumber>
</Identification>
<Name>
<LastName>STARR</LastName>
<FirstName>JEROME</FirstName>
<MiddleName>I</MiddleName>
</Name>
<Address>
<AddressLine1>67 BELMONT ST</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016050000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5087541707</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5083345331</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>90.000</Value>
</Quantity>
<DaysSupply>30</DaysSupply>
<Directions>TAKE 1 TABLET 3 TIMES A DAY</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>1</Quantity>
</Refills>
<WrittenDate>2011-01-15</WrittenDate>
<LastFillDate>2011-01-15</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>UNKNOWN</MutuallyDefined>
<DEANumber>BF2820199</DEANumber>
<StateLicenseNumber>.</StateLicenseNumber>
</Identification>
<Name>
<LastName>FAHEY</LastName>
<FirstName>DAVID</FirstName>
<MiddleName>A</MiddleName>
</Name>
<Address>
<AddressLine1>26 JULIO DR</AddressLine1>
<City>SHREWSBURY</City>
<State>MA</State>
<ZipCode>015450000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088425594</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5088420989</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>16.000</Value>
</Quantity>
<DaysSupply>25</DaysSupply>
<Directions>USE AS DIRECTED</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>5</Quantity>
</Refills>
<WrittenDate>2010-12-16</WrittenDate>
<LastFillDate>2010-12-16</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>UNKNOWN</MutuallyDefined>
<DEANumber>BF2820199</DEANumber>
<StateLicenseNumber>.</StateLicenseNumber>
</Identification>
<Name>
<LastName>FAHEY</LastName>
<FirstName>DAVID</FirstName>
<MiddleName>A</MiddleName>
</Name>
<Address>
<AddressLine1>26 JULIO DR</AddressLine1>
<City>SHREWSBURY</City>
<State>MA</State>
<ZipCode>015450000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088425594</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5088420989</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>ALEVE 225 MG TABLET</DrugDescription>
<DrugCoded>
<ProductCode>25866000003</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>60.000</Value>
</Quantity>
<DaysSupply>30</DaysSupply>
<Directions>TAKE 1 TABLET TWICE DAILY</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>5</Quantity>
</Refills>
<WrittenDate>2010-11-21</WrittenDate>
<LastFillDate>2010-11-21</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>1</MutuallyDefined>
<DEANumber>AC9136361</DEANumber>
<StateLicenseNumber>AC8912114</StateLicenseNumber>
</Identification>
<Name>
<LastName>CHITRE</LastName>
<FirstName>HARSHAD</FirstName>
<MiddleName>V</MiddleName>
</Name>
<Address>
<AddressLine1>20 WORCESTER BLVD</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016080000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5083636665</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5083636669</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>WARFARIN 10 MG TABLET</DrugDescription>
<DrugCoded>
<ProductCode>00555083502</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>60.000</Value>
</Quantity>
<DaysSupply>30</DaysSupply>
<Directions>TAKE 1 TABLET TWICE DAILY WITH MEALS</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>5</Quantity>
</Refills>
<WrittenDate>2010-10-15</WrittenDate>
<LastFillDate>2010-10-15</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>UNKNOWN</MutuallyDefined>
<DEANumber>AS5563386</DEANumber>
<StateLicenseNumber>99999999999</StateLicenseNumber>
</Identification>
<Name>
<LastName>STARR</LastName>
<FirstName>JEROME</FirstName>
<MiddleName>I</MiddleName>
</Name>
<Address>
<AddressLine1>67 BELMONT ST</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016050000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5087541707</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5083345331</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>COMFORT LANCETS</DrugDescription>
<DrugCoded>
<ProductCode>08189750510</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>60.000</Value>
</Quantity>
<DaysSupply>30</DaysSupply>
<Directions>TAKE 1 TABLET TWICE DAILY</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>5</Quantity>
</Refills>
<WrittenDate>2010-09-01</WrittenDate>
<LastFillDate>2010-09-01</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>UNKNOWN</MutuallyDefined>
<DEANumber>AC9136361</DEANumber>
<StateLicenseNumber>AC8912114</StateLicenseNumber>
</Identification>
<Name>
<LastName>CHITRE</LastName>
<FirstName>HARSHAD</FirstName>
<MiddleName>V</MiddleName>
</Name>
<Address>
<AddressLine1>20 WORCESTER BLVD</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016080000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5083636665</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5083636669</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>10.000</Value>
</Quantity>
<DaysSupply>2</DaysSupply>
<Directions>TAKE 1 TABLET EVERY 6 TO 8 HOURS AS NEEDED</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>0</Quantity>
</Refills>
<WrittenDate>2010-07-17</WrittenDate>
<LastFillDate>2010-07-17</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>1</MutuallyDefined>
<DEANumber>BB1486845</DEANumber>
</Identification>
<Name>
<LastName>BLINDER</LastName>
<FirstName>BORIS</FirstName>
<MiddleName>N</MiddleName>
</Name>
<Address>
<AddressLine1>580 LINCOLN ST</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016050000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088524646</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5087671757</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
<MedicationDispensed>
<DrugDescription>PREVACID 30 MG CAPSULE</DrugDescription>
<DrugCoded>
<ProductCode>00300304613</ProductCode>
<ProductCodeQualifier>ND</ProductCodeQualifier>
</DrugCoded>
<Quantity>
<Qualifier>ZZ</Qualifier>
<Value>10.000</Value>
</Quantity>
<DaysSupply>2</DaysSupply>
<Directions>TAKE 1 TABLET EVERY 6 TO 8 HOURS AS NEEDED</Directions>
<Refills>
<Qualifier>R</Qualifier>
<Quantity>0</Quantity>
</Refills>
<WrittenDate>2010-06-15</WrittenDate>
<LastFillDate>2010-06-15</LastFillDate>
<Pharmacy>
<Identification>
<NCPDPID>2201515</NCPDPID>
</Identification>
<Pharmacist>
<LastName>UNKNOWN</LastName>
</Pharmacist>
</Pharmacy>
<Prescriber>
<Identification>
<MutuallyDefined>UNKNOWN</MutuallyDefined>
<DEANumber>BB1486845</DEANumber>
</Identification>
<Name>
<LastName>BLINDER</LastName>
<FirstName>BORIS</FirstName>
<MiddleName>N</MiddleName>
</Name>
<Address>
<AddressLine1>580 LINCOLN ST</AddressLine1>
<City>WORCESTER</City>
<State>MA</State>
<ZipCode>016050000</ZipCode>
</Address>
<PhoneNumbers>
<Phone>
<Number>5088524646</Number>
<Qualifier>TE</Qualifier>
</Phone>
<Phone>
<Number>5087671757</Number>
<Qualifier>FX</Qualifier>
</Phone>
</PhoneNumbers>
</Prescriber>
</MedicationDispensed>
</RxHistoryResponse>
</Body>
</Message>

In Love... With Me!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-24 : 09:14:23
I assume you want to shred it and insert into a temp table? What are the columns you want to see in the temp table? In general, you would do for example, like this:

SELECT
   md.value('(./DrugDescription)[1]','varchar(255)') as col1
INTO #tmp
FROM
   @xmlvariable.nodes('//MedicationDispensed') T(md)
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

285 Posts
Posted - 2011-05-24 : 09:24:24
Yes Same thing I need but I have multiple MedicationDispensed in above xml
When I close all tags it Having 11 MedicationDispensed how to save in temp with 11 rows

This may be increased or decreased



In Love... With Me!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-24 : 09:33:35
I may not have understood exactly what you described above, but I copied the xml from your posting and ran the following query:

DECLARE @x XML;

SET @x = 'Copied your XML here';

WITH XMLNAMESPACES(DEFAULT 'http://www.ncpdp.org/schema/SCRIPT' )
SELECT
	md.value('(./DrugDescription)[1]','varchar(255)') as col1
INTO #tmp
FROM
	@x.nodes('//MedicationDispensed') T(md);
	
SELECT * FROM #tmp;

This gets the 11 rows in #tmp. Is that what you meant, or something else.

In my earlier posting, I had not included the namespace definition in the query - without that you would not get any rows.
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

285 Posts
Posted - 2011-05-24 : 09:39:55

Yes but I am getting only one column.
I need ProductCode,ProductCodeQualifier,Qualifier,Value..... Like this all column which is in tag MedicationDispensed



In Love... With Me!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-05-24 : 09:46:56
You can add the additional columns into the select query:

WITH XMLNAMESPACES(DEFAULT 'http://www.ncpdp.org/schema/SCRIPT' )
SELECT
	md.value('(./DrugDescription)[1]','varchar(255)') as col1,
	md.value('(./DrugCoded/ProductCodeQualifier)[1]','varchar(255)') as col2,
	md.value('(./Quantity/Qualifier)[1]','varchar(255)') as col3,
	md.value('(./Quantity/Value)[1]','varchar(255)') as col4
INTO #tmp
FROM
	@x.nodes('//MedicationDispensed') T(md);
If you had multiple quantities under the MedicationDispensed node, more work would be required, but in the example that I looked at, it didn't seem like there were.
Go to Top of Page

raghuveer125
Constraint Violating Yak Guru

285 Posts
Posted - 2011-05-24 : 09:50:52
Thank you Sunitbeck I will check. This is first time I am working with xml.
Thank you once again.

In Love... With Me!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site