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.
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 OtherDataFROM @X.nodes('/Object') T(c) CROSS APPLY c.nodes('SomeOtherData/Data')T2(c2); |
|
|
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] |
|
|
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. |
|
|
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. |
|
|
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 WithLabelsUnits214 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 |
|
|
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) CPIVOT (MAX(Data) FOR LocalName IN ([Name],[Value]))p)SELECT * FROM (SELECT [Name],[Value] FROM CTE2) C1PIVOT (MAX(Value) FOR Name IN ([Units],[Price], [MarketOrFirm], [ScheduledCommission], [ProposedCommission], [AdvisorId], [LanguageId], [WithLabels]))p[/CODE] |
|
|
|
|
|
|
|