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 column data

Author  Topic 

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-08-12 : 12:48:57
Hi,

I have an XML column which has logs from certain web requests.
I want to extract few information and show them in different columsn.

I can show "CallerFirmId" ID using this:

,[Body].value('(/LogMessage/Context/Object/CallerFirmId)[1]', 'varchar(max)') FirmID


but how do I find the AdvisorID, which shows as this in XML, as there are many Name, value pairs. THANK FOR YOUR HELP IN ADVANCE.

<NameValue>
<Name>AdvisorId</Name>
<Value>P1234</Value>
</NameValue>



<Context>
<Object xmlns="">
<CallerFirmId>19</CallerFirmId>
<QueryCount>1</QueryCount>
<RequestId>r163508246149769293758</RequestId>
<Token />
<RC>0</RC>
<ElapsedTimeMs>218</ElapsedTimeMs>
<MtTimeMs>234</MtTimeMs>
<DacTimeMs>233</DacTimeMs>
<ActiveCallCount>1</ActiveCallCount>
</Object>
</Context>
<RequestStart>2013-07-01T07:29:36.771875Z</RequestStart>
<RequestEnd>2013-07-01T07:29:36.990625Z</RequestEnd>
<RequestIp>20.20.20.20</RequestIp>
<RequestUri>/MYAPP/Api/MultiQuery</RequestUri>
<RequestData>
<MultiQueryRequest xmlns="http://schemas.datacontract.org/2004/07/myapp.something">
<LastClientSvcTimeMs>0</LastClientSvcTimeMs>
<LastRequestId xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
<Token xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
<Queries>
<MultiQueryItem>
<ClientRequestId>1</ClientRequestId>
<Params>
<NameValue>
<Name>Units</Name>
<Value>214</Value>
</NameValue>
<NameValue>
<Name>Price</Name>
<Value>396.15</Value>
</NameValue>
<NameValue>
<Name>MarketOrFirm</Name>
<Value>0</Value>
</NameValue>
<NameValue>
<Name>ScheduledCommission</Name>
<Value>100</Value>
</NameValue>
<NameValue>
<Name>ProposedCommission</Name>
<Value>100</Value>
</NameValue>
<NameValue>
<Name>AdvisorId</Name>
<Value>P1234</Value>
</NameValue>
<NameValue>
<Name>LanguageId</Name>
<Value>1</Value>
</NameValue>
<NameValue>
<Name>WithLabels</Name>
<Value>false</Value>
</NameValue>
</Params>
<ProcessName>MYPROCESS</ProcessName>
</MultiQueryItem>
</Queries>
</MultiQueryRequest>
</RequestData>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 13:17:02
Since your XML is not complete and not well-formed, I am unable to say anything more than general principles.

In general, one would navigate down to a node just above where your data resides in your from clause AND allows you to relate the various pieces of data that you want to pick up. Then get the data in the select clause by navigating to the node from that node.

The following is a simplified example that you can copy and paste to an SSMS query window to run and see what it does:
DECLARE @X XML  = '
<Object xmlns="">
<CallerFirmId>19</CallerFirmId>
<QueryCount>1</QueryCount>
<RequestId>r163508246149769293758</RequestId>
<Token />
<RC>0</RC>
<SomeOtherData>
<Data>1</Data>
<Data>2</Data>
<Data>3</Data>
</SomeOtherData>
<ElapsedTimeMs>218</ElapsedTimeMs>
<MtTimeMs>234</MtTimeMs>
<DacTimeMs>233</DacTimeMs>
<ActiveCallCount>1</ActiveCallCount>
</Object>

'
SELECT
c.value('./RequestId[1]','varchar(32)') AS RequestId,
c2.value('.','Int') AS OtherData
FROM
@X.nodes('/Object') T(c)
CROSS APPLY c.nodes('SomeOtherData/Data')T2(c2);
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 13:33:35
Or something like this:
[CODE]

DECLARE @x AS XML;
SET @x=N'
<Context>
<Object xmlns="">
<CallerFirmId>19</CallerFirmId>
<QueryCount>1</QueryCount>
<RequestId>r163508246149769293758</RequestId>
<Token />
<RC>0</RC>
<ElapsedTimeMs>218</ElapsedTimeMs>
<MtTimeMs>234</MtTimeMs>
<DacTimeMs>233</DacTimeMs>
<ActiveCallCount>1</ActiveCallCount>
</Object>
</Context>
<RequestStart>2013-07-01T07:29:36.771875Z</RequestStart>
<RequestEnd>2013-07-01T07:29:36.990625Z</RequestEnd>
<RequestIp>20.20.20.20</RequestIp>
<RequestUri>/MYAPP/Api/MultiQuery</RequestUri>
<RequestData>
<MultiQueryRequest xmlns="http://schemas.datacontract.org/2004/07/myapp.something">
<LastClientSvcTimeMs>0</LastClientSvcTimeMs>
<LastRequestId xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
<Token xmlns:i="http://www.w3.org/2001/XMLSchema-instance" i:nil="true" />
<Queries>
<MultiQueryItem>
<ClientRequestId>1</ClientRequestId>
<Params>
<NameValue>
<Name>Units</Name>
<Value>214</Value>
</NameValue>
<NameValue>
<Name>Price</Name>
<Value>396.15</Value>
</NameValue>
<NameValue>
<Name>MarketOrFirm</Name>
<Value>0</Value>
</NameValue>
<NameValue>
<Name>ScheduledCommission</Name>
<Value>100</Value>
</NameValue>
<NameValue>
<Name>ProposedCommission</Name>
<Value>100</Value>
</NameValue>
<NameValue>
<Name>AdvisorId</Name>
<Value>P1234</Value>
</NameValue>
<NameValue>
<Name>LanguageId</Name>
<Value>1</Value>
</NameValue>
<NameValue>
<Name>WithLabels</Name>
<Value>false</Value>
</NameValue>
</Params>
<ProcessName>MYPROCESS</ProcessName>
</MultiQueryItem>
</Queries>
</MultiQueryRequest>
</RequestData>';

;WITH CTE AS
(SELECT n.value('local-name(.)', 'VARCHAR(MAX)') AS LocalName,
n.value('.', 'VARCHAR(MAX)') AS Data,
ROW_NUMBER() OVER (ORDER BY d.n) - 1 AS rn
FROM @x.nodes('/RequestData/*/*/*/*/*/*') AS d(n))
SELECT Data From CTE WHERE rn = (SELECT rn from CTE WHERE Data = 'AdvisorId') +1;

[/CODE]
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-08-12 : 13:33:57
Thanks a lot James for your reply. I will check your example and see if I can get values from name value pairs inside XML nodes.
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-08-12 : 13:40:26
Thanks a lot MuMu88. I will give that a shot as well. Much appreciated.
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-08-12 : 15:13:56
Hi Mumu88,

Thanks. I used your code and I was able to get these for every request. (below code).

As you can, I need the data to be on a column so the best I can do is use the code below and get column as as this:


Units Price MarketOrFirm ScheduledCommission ProposedCommission AdvisorId LanguageId WithLabels
Units214 Price396.15 MarketOrFirm0 ScheduledCommission100 ProposedCommission100 AdvisorIdP1234 LanguageId1 WithLabelsfalse





select
@x.value('(/RequestData/*/*/*/*/*)[1]','varchar(max)') Units,
@x.value('(/RequestData/*/*/*/*/*)[2]','varchar(max)') Price,
@x.value('(/RequestData/*/*/*/*/*)[3]','varchar(max)') MarketOrFirm,
@x.value('(/RequestData/*/*/*/*/*)[4]','varchar(max)') ScheduledCommission,
@x.value('(/RequestData/*/*/*/*/*)[5]','varchar(max)') ProposedCommission,
@x.value('(/RequestData/*/*/*/*/*)[6]','varchar(max)') AdvisorId,
@x.value('(/RequestData/*/*/*/*/*)[7]','varchar(max)') LanguageId,
@x.value('(/RequestData/*/*/*/*/*)','varchar(max)') WithLabels



Which is not bad. But Is there way to just to show the Value? I know I can use the code where you are using a CTE to get the value, but I need to display all the values for each request to display in a row. So I can't use CTE on each request. Is there a simpler way?

Thanks again.

D
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 21:28:43
Try this:
[CODE]

;WITH CTE AS
(SELECT n.value('local-name(.)', 'VARCHAR(MAX)') AS LocalName,
n.value('.', 'VARCHAR(MAX)') AS Data,
ROW_NUMBER() OVER (ORDER BY d.n) AS rn1
FROM @x.nodes('/RequestData/*/*/*/*/*/*') AS d(n)),
CTE1 AS
(SELECT ROW_NUMBER() OVER (PARTITION BY C.LocalName ORDER BY rn1) AS rn, LocalName, Data FROM CTE C),
CTE2 AS
(SELECT rn, [Name], [Value] FROM
(SELECT rn, LocalName, Data FROM CTE1) C
PIVOT (MAX(Data) FOR LocalName IN ([Name],[Value]))p)
SELECT * FROM
(SELECT [Name],[Value] FROM CTE2) C1
PIVOT (MAX(Value) FOR Name IN ([Units],[Price], [MarketOrFirm], [ScheduledCommission], [ProposedCommission], [AdvisorId], [LanguageId], [WithLabels]))p

[/CODE]
Go to Top of Page
   

- Advertisement -