need help regarding querying xml in sql server

Question

i have two table in sql server 2005. in one table table1 there is one column whose data type is xml where we save data in xml format. now i have another table table2 where we store few fileds name. so now i want to write query in such a on xml data which will return those fields value which are defined in table2. how to achieve it with simple sql statement instead of store procedure.

my xml structure and data like below one

<Record xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <DELETED>
    <JID>41185</JID>
    <WID>0</WID>
    <AccountReference>LH169</AccountReference>
    <OEReference>Ari002</OEReference>
    <InvoiceNumber>0</InvoiceNumber>
    <OrderPlacedBy>Mark Catterall</OrderPlacedBy>
    <Specialist>0FFICINA MOTTAUTO</Specialist>
    <Priority>2</Priority>
    <JobType>OUR</JobType>
    <JobState>NOTSTARTED</JobState>
    <JobAddedDate>2011-05-31T16:17:00</JobAddedDate>
    <JobStartedDate>2011-05-31T16:18:00</JobStartedDate>
    <JobFinishedDate>1777-01-01T00:00:01</JobFinishedDate>
    <JobShippedDate>1777-01-01T00:00:01</JobShippedDate>
    <RecievedDate>1777-01-01T00:00:01</RecievedDate>
    <UPSShippingNumber />
    <CustomerName>02 IRELAND</CustomerName>
    <ContactName>ALAN CONLAN</ContactName>
    <Telephone>00353868377926</Telephone>
    <StandardHours>3.00</StandardHours>
    <JobDescription>test for search 2</JobDescription>
    <UserName xsi:nil="true" />
    <AwaitingCustomer>0</AwaitingCustomer>
    <ReturnToCore>0</ReturnToCore>
    <AwaitingFromSalvage>0</AwaitingFromSalvage>
    <PartDescription>test for search 2</PartDescription>
    <PostalCode>IRELAND</PostalCode>
    <OURPrice xsi:nil="true" />
    <ExchangePrice xsi:nil="true" />
    <NewPrice xsi:nil="true" />
    <eBayPrice xsi:nil="true" />
    <Status>UPDATED</Status>
  </DELETED>
</Record>

suppose in my table2 few fields are stored like JID,WID,AccountReference,OEReference,InvoiceNumber.

so please guide me how to write sql on xml data which will return only JID,WID,AccountReference,OEReference,InvoiceNumber from xml data but filed name will not be hard coded rather it will be fetch from another table table2.

please guide me.

ibram · Accepted Answer · 2011-06-01 08:29:25Z

1

To read data from xml you can use like this:

    Select
    MyXmlColumn.value('(Record/DELETED/JID)[1]', 'int' ) as JID,
    MyXmlColumn.value('(Record/DELETED/WID)[1]', 'int' ) as WID,
    MyXmlColumn.value('(Record/DELETED/AccountReference)[1]', 'nvarchar(255)' ) as AccountReference from table2

[update]

create a stored procedure with your parameters:

create procedure getmyxmldata
(
    @param1 varchar(50),
    @param2 varchar(50)
)
as
begin    
    declare @myQuery varchar(1000);
    set @myQuery = 'Select
        MyXmlColumn.value(''(Record/DELETED/' + @param1 + ')[1]'', ''nvarchar(255)'' ) as ' + @param1 + ',
        MyXmlColumn.value(''(Record/DELETED/' + @param1 + ')[1]'', ''nvarchar(255)'' ) as ' + @param1 + ' from table2';

EXEC sp_executesql @myQuery
end

It is also possible to read the specified fields from the other table in this stored procedure and create your select statement dynamically without passing parameters.

[Update 2]

you can try this for multiple deleted tags:

Select
Row.value('(./JID)[1]', 'int' ) as JID,
Row.value('(./WID)[1]', 'int' ) as WID,
Row.value('(./AccountReference)[1]', 'nvarchar(255)' ) as AccountReference 
from table2
CROSS APPLY MyXmlColumn.nodes('/Record/DELETED') as Record(Row)

edited Jun 1, 2011 at 8:29

answered Jun 1, 2011 at 6:29

ibram

4,6393 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thomas Over a year ago

No i do not want to hard code the field name rather fields name are stored in another table so those field name should be fetched in sql instead of hard coded field name. how to do it. please advise

Thomas Over a year ago

how to query if there is more record in xml field suppose there are many <DELETED></DELETED> means many record. here you specify first record MyXmlColumn.value('(Record/DELETED/JID)[1]', 'int' ) as JID, you mention [1] means fetch only first record...if there are many records then how to do it. please help

Thomas Over a year ago

what is CROSS APPLY...i am not familier with CROSS APPLY. can u please explain when CROSS APPLY is required and how it works. thanks

ibram Over a year ago

Here is a good article: sqlteam.com/article/…

Collectives™ on Stack Overflow

need help regarding querying xml in sql server

my xml structure and data like below one

1 Answer 1

[update]

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

my xml structure and data like below one

1 Answer 1

[update]

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related